Convert an XML string into a Dataset

By Ken Fitzpatrick
ODBC Drivers for QuickBooks, Salesforce, SAP, MSCRM, SharePoint … Free Trial!

The following code will convert a string of XML into a Dataset, as long as the XML is structured like a dataset in that it has a collection of tables which contain a collection of rows which contain a collection of columns. The second parameter is the path/filename of a schema file (XSL) which will help out with relationships between tables or if datatypes must be preserved. Passing an empty string as the SchemaFile pathname will cause the function to Infer the Schema automatically.

    Public Shared Function XMLToDataSet(ByVal xmlStr As String, ByVal schemaFile As String) As DataSet
        'Convert the XML to a dataset
        Dim sr As New StringReader(xmlStr)

        'Convert xmlData to a Dataset
        Dim ds As New DataSet

        If schemaFile = String.Empty Then
            ds.ReadXml(sr, XmlReadMode.InferSchema)
        Else
            ds.ReadXmlSchema(schemaFile)
            ds.ReadXml(sr, XmlReadMode.ReadSchema)
        End If

        For Each relation As DataRelation In ds.Relations
            For Each c As DataColumn In relation.ParentColumns
                If Not relation.ChildTable.Columns.Contains(c.ColumnName) Then
                    relation.ChildTable.Columns.Add(c)
                End If
                For Each dr As DataRow In relation.ChildTable.Rows
                    dr(c.ColumnName) = dr.GetParentRow(relation)(c.ColumnName)
                Next
            Next
        Next

        Return ds
    End Function

Related FAQs

Recursively walking the DOM of an XMLNode or XMLDocument
The following two overloaded shared functions (static methods) can be used to either return a list of all nodes in an XMLDocument or walk the DOM of an XMLNode and return a string representation of it. The first function "DisplayXMLNodes(ByVal xmlStr As String) As String" makes use of the second function "DisplayXMLNodes(ByVal xmlNode As XmlNode, ByVal indent As Integer) As String" which calls itself recursively.
How to change the row color of a Gridview row
The following is snippet of code from a RowDataBound event handler for a Gridview named Gridview1. To change the background color of a Gridview row, you need to add a handler for the RowDataBound event. This event is called for each row added to a datagrid whem it is being bound from the datasource. Use the condition "If e.Row.RowType = DataControlRowType.DataRow" to only trap when a data row is being written as opposed to a header row or footer row.
Convert an XML string into a Dataset  (1052 Views)