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

Last Reply 7 days ago By pandeyism

Posted 7 days ago

I am developing a console application to consume web services, I am based on this example

https://www.c-sharpcorner.com/article/calling-web-service-using-soap-request/

How to import XML to DataTable returned when consuming a web service

what I would like to know is if the result that it returns can be saved in a table of the database, here my code of what I was testing: 

public void saveDatos(string xmlFile)
{
    string connection = "Data Source=10.125.265.232;Initial Catalog=ABinBev;Persist Security Info=True;User ID=***;Password=********";
    try
    {
        //Convert XML to Datable 
        DataTable dataTable = CreateDataTableXML(xmlFile);
        if (dataTable.Columns.Count == 0)
        {
            dataTable.ReadXml(xmlFile);
        }
        // Copy Data from DataTable to Sql Table 
        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 = "borrarPrueba_WS";
                bulkCopy.WriteToServer(dataTable);
            } con.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}
private DataTable CreateDataTableXML(string xmlFile)
{
    DataTable dt = new DataTable();
    XmlDocument xml = new XmlDocument();
    try
    {
        //dt.TableName = GetTableName(XmlFile); 
        XmlNode nodoEstructura = xml.DocumentElement.ChildNodes.Cast().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)
        {
            List Valores = fila.ChildNodes.Cast().ToList().Select(x => x.InnerText).ToList();
            dt.Rows.Add(Valores.ToArray());
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return dt;
}

Or in what way I could do this since I was testing and it did not work.

Posted 7 days ago

Hi fredo1094,

Refer below sample.

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