How to display DataTable XML from WriteXml in a ni

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>

Answer 1

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>

Answer 2

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();
}