Read xml, xsd file and insert data to Database in ASP.Net

nadeem12...

SOLVED CLOSED ACTIVE ANSWERED
User: nadeem1218
Posted: on Jul 19, 2018 06:23 AM
Forum: Data Controls
Answer: 1
Views: 13385
Sample Code: Download

Hello,

I have the below xml & xsd files and need to upload the data to sql server.

Can anyone help to how can I acheive this using asp.net. I have followed several articles & all of them are using simple xml file not like the one below.

xsd is only for table creation and how to insert this xml data to database?

XSD FILE CONTENT

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:tns="http://www.tous.com/customers"
	xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.tous.com/customers">
	<xsd:element name="customers" type="tns:customers">
		<xsd:annotation>
			<xsd:documentation>Root element</xsd:documentation>
		</xsd:annotation>
	</xsd:element>
	<xsd:complexType name="customers" id="customers">
		<xsd:sequence>
			<xsd:element name="customer" type="tns:customer"
				maxOccurs="unbounded" />
		</xsd:sequence>
	</xsd:complexType>
	<xsd:complexType name="customer" id="customer">
		<xsd:sequence>
			<xsd:element name="store" type="xsd:string">
				<xsd:annotation>
					<xsd:documentation>store (4 characters)</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="code" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Client code</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="name" type="xsd:string">
				<xsd:annotation>
					<xsd:documentation>Customer Name</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="street" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>street</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="postalCode" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Postal code</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="town" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Town</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="country" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Country code (ES,PT,..)</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="region" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Region</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="language" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>language</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="phone" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>phone</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="email" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>email</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="indexTypeDocumentID" type="xsd:integer" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>
						NULL
						1 - DNI/NIE
						2 - Passport
						3 - CIF
						5 - Doc. Id. extranjero
					</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="typeDocumentID" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Tyep document ID</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="nif" type="xsd:string">
				<xsd:annotation>
					<xsd:documentation>NIF</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="nifCommunity" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Nif community</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="society" type="xsd:integer">
				<xsd:annotation>
					<xsd:documentation>society</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="employee" type="xsd:boolean">
				<xsd:annotation>
					<xsd:documentation>Employee</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="gender" type="xsd:string" minOccurs="0">
				<xsd:annotation>
					<xsd:documentation>Gender</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
			<xsd:element name="purpose" type="xsd:integer">
				<xsd:annotation>
					<xsd:documentation>
						1 . Credit
						2 . Invoice
					</xsd:documentation>
				</xsd:annotation>
			</xsd:element>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>

XML FILE CONTENT

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<ns2:customers xmlns:ns2="http://www.tous.com/customers">
-<customer>

<store>0020</store>

<code>6661207</code>

<name>MUÑOZ VERA, RAUL</name>

<street>CARRER SANT JOSEP, 53 2N 2ª </street>

<postalCode>08242</postalCode>

<town>MANRESA</town>

<country>ES</country>

<region/>

<language>ES</language>

<phone>659712887</phone>

<email>rmunoz@raulmuve.cat</email>

<typeDocumentID/>

<nif>39374240B</nif>

<nifCommunity/>

<society>38</society>

<employee>true</employee>

<gender>M</gender>

<purpose>2</purpose>

</customer>


-<customer>

<store>0020</store>

<code>6661207</code>

<name>MUÑOZ VERA, RAUL</name>

<street>CARRER SANT JOSEP, 53 2N 2ª </street>

<postalCode>08242</postalCode>

<town>MANRESA</town>

<country>ES</country>

<region/>

<language>ES</language>

<phone>659712887</phone>

<email>rmunoz@raulmuve.cat</email>

<typeDocumentID/>

<nif>39374240B</nif>

<nifCommunity/>

<society>38</society>

<employee>true</employee>

<gender>M</gender>

<purpose>2</purpose>

</customer>


-<customer>

<store>0020</store>

<code>6661207</code>

<name>MUÑOZ VERA, RAUL</name>

<street>CARRER SANT JOSEP, 53 2N 2ª </street>

<postalCode>08242</postalCode>

<town>MANRESA</town>

<country>ES</country>

<region/>

<language>ES</language>

<phone>659712887</phone>

<email>rmunoz@raulmuve.cat</email>

<typeDocumentID/>

<nif>39374240B</nif>

<nifCommunity/>

<society>38</society>

<employee>true</employee>

<gender>M</gender>

<purpose>2</purpose>

</customer>


-<customer>

<store>0020</store>

<code>6661207</code>

<name>MUÑOZ VERA, RAUL</name>

<street>CARRER SANT JOSEP, 53 2N 2ª </street>

<postalCode>08242</postalCode>

<town>MANRESA</town>

<country>ES</country>

<region/>

<language>ES</language>

<phone>659712887</phone>

<email>rmunoz@raulmuve.cat</email>

<typeDocumentID/>

<nif>39374240B</nif>

<nifCommunity/>

<society>38</society>

<employee>true</employee>

<gender>M</gender>

<purpose>2</purpose>

</customer>


-<customer>

<store>0020</store>

<code/>

<name>Raul Muñoz</name>

<street>Calle La Pau</street>

<postalCode>08242</postalCode>

<town>Manresa</town>

<country/>

<region/>

<language>ES</language>

<phone>0</phone>

<email/>

<typeDocumentID/>

<nif>39374240B</nif>

<nifCommunity/>

<society>38</society>

<employee>false</employee>

<gender/>

<purpose>2</purpose>

</customer>


-<customer>

<store>0020</store>

<code>6661207</code>

<name>MUÑOZ VERA, RAUL</name>

<street>CRER SANT JOSEP 53 P02 2</street>

<postalCode>08242</postalCode>

<town>MANRESA</town>

<country>ES</country>

<region>08</region>

<language>ES</language>

<phone>659712887</phone>

<email>rmunoz@raulmuve.cat</email>

<indexTypeDocumentID>1</indexTypeDocumentID>

<typeDocumentID>DNI/NIE</typeDocumentID>

<nif>39374240B</nif>

<nifCommunity>ES39374240B</nifCommunity>

<society>38</society>

<employee>true</employee>

<gender>M</gender>

<purpose>1</purpose>

</customer>

</ns2:customers>

Thanks

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 8

Reply
ANSWER
User: dharmendr
Replied: on Jul 23, 2018 08:05 AM
Report

Hi nadeem1218,

Check this example. Now please take its reference and correct your code.

Namespaces

using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Xml
Imports System.Xml.Linq
Imports System.Configuration
Imports System.Data.SqlClient

Code

protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("~/Test.xsd"));
    string tableName = ds.Tables[0].TableName;
    DataTable dt = ds.Tables[1];
    XmlDocument doc = new XmlDocument();
    doc.Load(Server.MapPath("~/Test.xml"));
    XmlNamespaceManager xnm = new XmlNamespaceManager(doc.NameTable);
    xnm.AddNamespace("ns2", "http://www.tous.com/customers");
    XmlNodeList xNodes = doc.SelectSingleNode("ns2:customers", xnm).ChildNodes;

    for (int i = 0; i < xNodes.Count; i++)
    {
        DataRow dr = dt.NewRow();
        for (int j = 0; j < xNodes[i].ChildNodes.Count; j++)
        {
            string nodeName = xNodes[i].ChildNodes[j].LocalName;
            string value = xNodes[i].ChildNodes[j].InnerText;
            dr[nodeName] = value;
        }
        dt.Rows.Add(dr);
    }
    // Insert record to database.
    if (dt.Rows.Count > 0)
    {
        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.Customers";
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim ds As DataSet = New DataSet()
    ds.ReadXml(Server.MapPath("~/Test.xsd"))
    Dim tableName As String = ds.Tables(0).TableName
    Dim dt As DataTable = ds.Tables(1)
    Dim doc As XmlDocument = New XmlDocument()
    doc.Load(Server.MapPath("~/Test.xml"))
    Dim xnm As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)
    xnm.AddNamespace("ns2", "http://www.tous.com/customers")
    Dim xNodes As XmlNodeList = doc.SelectSingleNode("ns2:customers", xnm).ChildNodes

    For i As Integer = 0 To xNodes.Count - 1
        Dim dr As DataRow = dt.NewRow()
        For j As Integer = 0 To xNodes(i).ChildNodes.Count - 1
            Dim nodeName As String = xNodes(i).ChildNodes(j).LocalName
            Dim value As String = xNodes(i).ChildNodes(j).InnerText
            dr(nodeName) = value
        Next
        dt.Rows.Add(dr)
    Next
    ' Insert record to database.
    If dt.Rows.Count > 0 Then
        Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(consString)
            Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
                sqlBulkCopy.DestinationTableName = "dbo.Customers"
                con.Open()
                sqlBulkCopy.WriteToServer(dt)
                con.Close()
            End Using
        End Using
    End If
End Sub

For more details on SQLBulkCopy refer below article.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Read xml, xsd file and insert data to Database in ASP.Net

SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net

Company

Explore

Follow us

Contact Us