How to display DataTable XML from WriteXml in a nicely readable format?

Question

I have some DataTables that I want to export to a file and then look at. I can use DataTable.WriteXml() to output the contents of the DataTables to XML but what's an easy way to display this data in tabular form?

One suggested way is to transform the XML using XSLT or similar into something readable. I don't have any idea if that's easy or not but that'd be a fine solution if it's easy to use.

An example of the XML I get from WriteXml() for a single DataTable is below. This is for a table named 'RecentMatter' with 10 columns and 3 rows of data. I'd like to have it displayed like a table of data. I'm less concerned about formatting of data values, e.g. converting DateTime values to more readable values. I used the XmlWriteMode.WriteSchema parameter when generating this. If you remove that then all the schema info (before the first tag) is removed.

Different tables will obviously have different numbers of columns, so any solution would need to deal with that, ideally without having to modify the solution for tables with different columns.

<NewDataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="RecentMatter" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="RecentMatter">
<xs:complexType>
<xs:sequence>
<xs:element name="UserLogin">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MatterNumber">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ClientName">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MatterName">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ClientCode" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OfficeCode" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OfficeName" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Billable" type="xs:boolean"/>
<xs:element name="ReferenceId" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LastUsed" type="xs:dateTime"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//RecentMatter"/>
<xs:field xpath="UserLogin"/>
<xs:field xpath="MatterNumber"/>
</xs:unique>
</xs:element>
</xs:schema>

<RecentMatter>
  <UserLogin>PSLTP6\RJK</UserLogin>
  <MatterNumber>99999-2302</MatterNumber>
  <ClientName>Test Matters</ClientName>
  <MatterName>DP Test Matter</MatterName>
  <ClientCode>99999</ClientCode>
  <OfficeCode/>
  <OfficeName/>
  <Billable>true</Billable>
  <ReferenceId/>
  <LastUsed>2011-08-23T23:40:24.13+01:00</LastUsed>
</RecentMatter>
<RecentMatter>
  <UserLogin>PSLTP6\RJK</UserLogin>
  <MatterNumber>999991.0002</MatterNumber>
  <ClientName>Lathe 1</ClientName>
  <MatterName>LW Test 2</MatterName>
  <ClientCode/>
  <OfficeCode/>
  <OfficeName/>
  <Billable>true</Billable>
  <ReferenceId/>
  <LastUsed>2011-07-12T16:57:27.173+01:00</LastUsed>
</RecentMatter>
<RecentMatter>
  <UserLogin>PSLTP6\RJK</UserLogin>
  <MatterNumber>999991-0001</MatterNumber>
  <ClientName>Lathe 1</ClientName>
  <MatterName>LW Test 1</MatterName>
  <ClientCode/>
  <OfficeCode/>
  <OfficeName/>
  <Billable>false</Billable>
  <ReferenceId/>
  <LastUsed>2011-07-12T01:59:06.887+01:00</LastUsed>
</RecentMatter>
</NewDataSet>

did you try to open those generated XML with excel? I think you should see it as table, at least with recent versions of Excel. — Davide Piras, Sep 02 '11 at 11:44
maybe you should do XMLWriter.Formatting = Formatting.Indented; i don't know if you can Store DataTable to XmlWriter ,if yes than this should construct you're xml. — Rosmarine Popcorn, Sep 02 '11 at 11:50
Opening in Excel doesn't work very well: if you don't include Xml Schema info in the XML then it will display a table but without column headers. Including xml schema makes it go completely awry. It also freaks out with large values and doesn't deal with DataSets correctly, putting them all adjacent to each other on one wide sheet. — Rory, Sep 02 '11 at 12:15

James Johnson · Answer 1 · 2011-09-07T15:07:46.567

Give this a shot. Probably the easiest way to serialize a DataTable to XML:

DataTable table = new DataTable();    
System.IO.StringWriter writer = new System.IO.StringWriter();

//notice that we're ignoring the schema so we get clean XML back
//you can change the write mode as needed to get your result
table.WriteXml(writer, XmlWriteMode.IgnoreSchema, false);

string dataTableXml = writer.ToString();

As for displaying it in a readable format, I would suggest passing the XML into an XSL transformer, which you can then use to parse the XML and manipulate the output as needed.

Applying an XSLT Transform to a DataSet

http://msdn.microsoft.com/en-us/library/8fd7xytc%28v=vs.71%29.aspx#Y289

Here's a simple example I created to explain how you would use the XSL transformer. I haven't tested it, but it should be pretty close:

DataSet ds = new DataSet();
StringBuilder sbXslOutput = new StringBuilder();

using (XmlWriter xslWriter = XmlWriter.Create(sbXslOutput))
{
    XslCompiledTransform transformer = new XslCompiledTransform();
    transformer.Load("transformer.xsl");
    XsltArgumentList args = new XsltArgumentList();

    transformer.Transform(new XmlDataDocument(ds), args, xslWriter);
}

string dataSetHtml = sbXslOutput.ToString();

Formatting XML as HTML using XSLT

Here's an example of using XSLT to transform XML into an HTML table. It should be fairly easy to adopt so you can use it with your serialized DataSet.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
      <table border="1">
        <tr>
          <th>User Login</th>
          <th>Matter Number</th>
          ...
        </tr>
        <xsl:for-each select="NewDataSet/RecentMatter">
          <tr>
            <td>
              <xsl:value-of select="UserLogin"/>
            </td>
            <td>
              <xsl:value-of select="MatterNumber"/>
            </td>
            ...
          </tr>
        </xsl:for-each>
      </table>
  </xsl:template>
</xsl:stylesheet>

Sure, that's the easy bit, it's the "passing the XML into an XSL transformer, which you can then use to parse the XML and manipulate the output as needed" that's the magic I'm looking for :) — Rory, Sep 07 '11 at 10:52
@Rory: Can you edit your question and include the some of the XML from the serialized DataTable? And also, what you are looking for in terms of output? — James Johnson, Sep 07 '11 at 13:21
sure, done. I'd love to see an XSLT example for transforming it to be readable. — Rory, Sep 07 '11 at 14:25

score 0 · Answer 2 · answered Sep 02 '11 at 13:29

This method I built may be useful for you (can remove dash decoration if you like...). It should render table like this:

+----------+----------+----------+
| Column1  | Column2  | Column3  |
+----------+----------+----------+
| asdfasdf | asdfasdf | asdfasdf |
| asdfasdf | asdfasdf | asdfasdf |
| asdfasdf | asdfasdf | asdfasdf |
+----------+----------+----------+

Here's the code:

public void WriteTable(DataTable table, Stream outputStream)
{
    // Create writer
    StreamWriter writer = new StreamWriter(outputStream);

    // Compute max lengths to build correct tabular columns
    Dictionary<string, int> maxLengths = new Dictionary<string, int>();

    foreach (DataColumn column in table.Columns)
    {
        maxLengths.Add(column.ColumnName, column.ColumnName.Length);

        foreach (DataRow row in table.Rows)
        {
            maxLengths[column.ColumnName] = Math.Max(
                maxLengths[column.ColumnName], 
                Convert.ToString(row[column.ColumnName]).Length);
        }
    }

    // Build horizontal rule to separate headers from data
    string horizontalRule = "+";

    foreach (DataColumn column in table.Columns)
    {
        horizontalRule += String.Format("-{0}-+", new string('-', maxLengths[column.ColumnName]));
    }

    writer.WriteLine(horizontalRule);

    // Write headers
    writer.Write("|");

    foreach (DataColumn column in table.Columns)
    {
        writer.Write(" {0," + (-(maxLengths[column.ColumnName])) + "} |", column.ColumnName);
    }

    writer.WriteLine();

    writer.WriteLine(horizontalRule);

    // Write data
    foreach (DataRow row in table.Rows)
    {
        writer.Write("|");

        foreach (DataColumn column in table.Columns)
        {
            writer.Write(" {0," + (-(maxLengths[column.ColumnName])) + "} |", row[column.ColumnName]);
        }

        writer.WriteLine();
    }

    writer.WriteLine(horizontalRule);

    // Close writer
    writer.Close();
}

How to display DataTable XML from WriteXml in a nicely readable format?

2 Answers2

Linked