Convert XML Data returned from Web Service to DataTable using C# and VB.Net in ASP.Net

Hi fredo1094,

DataSet and XmlDocument class are used to read the xml data.

Refer below sample and correct your code.

Namespaces

C#

using System.Data;
using System.Xml;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    saveDatos(Server.MapPath("XMLFile.xml"));
}

public void saveDatos(string xmlFile)
{
    try
    {
        DataTable dataTable = CreateDataTableXML(xmlFile);
        if (dataTable.Columns.Count == 0)
        {
            dataTable.ReadXml(xmlFile);
        }
        else
        {
            using (SqlConnection con = new SqlConnection(connection))
            {
                using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity))
                {
                    con.Open();
                    foreach (DataColumn col in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.BulkCopyTimeout = 600; bulkCopy.DestinationTableName = "CustomerTest";
                    bulkCopy.WriteToServer(dataTable);
                }
                con.Close();
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

private DataTable CreateDataTableXML(string xmlFile)
{
    DataTable dt = new DataTable();
    try
    {
        DataSet ds = new DataSet();
        ds.ReadXml(xmlFile);
        XmlDocument xml = new XmlDocument();
        xml.LoadXml(ds.GetXml());
        XmlNode nodoEstructura = xml.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];
        foreach (XmlNode columna in nodoEstructura.ChildNodes)
        {
            dt.Columns.Add(columna.Name, typeof(String));
        }
        XmlNode filas = xml.DocumentElement;
        foreach (XmlNode fila in filas.ChildNodes)
        {
            dt.Rows.Add(fila["CustomerId"].InnerText, fila["Name"].InnerText, fila["Country"].InnerText);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return dt;
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
    saveDatos(Server.MapPath("XMLFile.xml"))
End Sub

Public Sub saveDatos(ByVal xmlFile As String)
    Try
        Dim dataTable As DataTable = CreateDataTableXML(xmlFile)
        If dataTable.Columns.Count = 0 Then
            dataTable.ReadXml(xmlFile)
        Else
            Using con As SqlConnection = New SqlConnection(connection)
                Using bulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity)
                    con.Open()
                    For Each col As DataColumn In dataTable.Columns
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)
                    Next
                    bulkCopy.BulkCopyTimeout = 600
                    bulkCopy.DestinationTableName = "CustomerTest"
                    bulkCopy.WriteToServer(dataTable)
                End Using
                con.Close()
            End Using
        End If
    Catch ex As Exception
        Throw ex
    End Try
End Sub

Private Function CreateDataTableXML(ByVal xmlFile As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Try
        Dim ds As DataSet = New DataSet()
        ds.ReadXml(xmlFile)
        Dim xml As XmlDocument = New XmlDocument()
        xml.LoadXml(ds.GetXml())
        Dim nodoEstructura As XmlNode = xml.DocumentElement.ChildNodes.Cast(Of XmlNode)().ToList()(0)
        For Each columna As XmlNode In nodoEstructura.ChildNodes
            dt.Columns.Add(columna.Name, GetType(String))
        Next
        Dim filas As XmlNode = xml.DocumentElement
        For Each fila As XmlNode In filas.ChildNodes
            dt.Rows.Add(fila("CustomerId").InnerText, fila("Name").InnerText, fila("Country").InnerText)
        Next
    Catch ex As Exception
        Throw ex
    End Try

    Return dt
End Function