CODEWITHSUNDEEP
c#sqlxml
Cát Tường Vy
Cát Tường Vy

Reputation: 398

Load XML file into SQL Server tables using C#

I have complex XML file that export from old program in VB6, now I must write a program in C# to load this XML file into 2 tables. Here is the XML file structure:

<?xml version="1.0" encoding="SHIFT-JIS" standalone="yes"?>
<AdminFile>
    <PO_No></PO_No>
    <Database>
        <PO>
            <Table_PO_Master>
                <DATA>
                    <PO_No></PO_No>
                    <PO_Date></PO_Date>
                </DATA>             
            </Table_PO_Master>
            <Table_PO_Details>
                <DATA>
                    <PO_No></PO_No>
                    <GOODS_CD></GOODS_CD>
                    <QTY></QTY>
                </DATA>             
            </Table_PO_Details>
        </PO>
    </Database>
</AdminFile>

I need to load this XML into 2 SQL Server tables that have the structure as below:

Table_PO_Master(Id,PO_No,PO_Date)

Table_PO_Details(PO_No,GOODS_CD,QTY)

I googled and found this code but do not know to to apply it in my case.

string myXMLfile = "D:\\MyXML.xml";
XmlTextReader textReader = new XmlTextReader(myXMLfile);
                XmlDocument doc = new XmlDocument();
                doc.Load(myXMLfile);
                XmlNodeList List = doc.GetElementsByTagName("RWResponse");

                foreach (XmlNode node in List)
                {
                    XmlNode Element = (XmlNode)node;
                    foreach (XmlNode node1 in Element)
                    {
                        XmlNode Element1 = (XmlNode)node1;
                        foreach (XmlNode node2 in Element1)
                        {
                            XmlNode Element2 = (XmlNode)node2;
                            foreach (XmlNode node3 in Element2)
                            {
                                XmlNode Element3 = (XmlNode)node3;
                                if (Element3.Name.ToUpper() != "HEADER")
                                {
                                    if (!Element3.OuterXml.ToString().Contains("ROW type=\"subtotal\""))
                                    {
                                        if (!Element3.OuterXml.ToString().Contains("ROW type=\"total\""))
                                        {
                                            DataRow dr = ret_XML_Data_in_DataTable.NewRow();                                           
                                            foreach (XmlNode node4 in Element3)
                                            {
                                                XmlElement Element4 = (XmlElement)node4;

                                            }
                                         }
                                    }
                                }
                            }
                        }
                    }
                }
            }

Upvotes: 1

Views: 5573

Answers (2)

Daniel B
Daniel B

Reputation: 3185

You can also use XDocument:

var doc = XDocument.Parse(xml);
foreach (XElement xe in doc.Descendants("Table_PO_Master"))
{    
    var PO_No = xe.Element("DATA").Element("PO_No").Value;
    var PO_Date = xe.Element("DATA").Element("PO_Date").Value;
    SaveIntoMaster(PO_No, PO_Date);
}

and do the same for Table_PO_Details

Upvotes: 1

programtreasures
programtreasures

Reputation: 4298

If you want to save data by SP than you need not to load xml in your c# application,

Pass your xml as a store procedure parameter usingo ado.net,

After you can extract xml and insert the data

so your SP is like,

CREATE PROCEDURE [dbo].[InsertData]
@xml XML
AS
BEGIN

INSERT INTO Table_PO_Master
      SELECT
      PO_Master.value('(PO_No/text())[1]','VARCHAR(100)') AS PO_No, --TAG
      PO_Master.value('(PO_Date/text())[1]','VARCHAR(100)') AS PO_Date --TAG
      FROM
      @xml.nodes('/AdminFile/Database/PO/Table_PO_Master/Data')AS TEMPTABLE(PO_Master)

INSERT INTO Table_PO_Details
      SELECT
      PO_Master.value('(PO_No/text())[1]','VARCHAR(100)') AS PO_No, --TAG
      PO_Master.value('(GOODS_CD/text())[1]','VARCHAR(100)') AS GOODS_CD --TAG
      PO_Master.value('(QTY/text())[1]','int') AS QTY --TAG
      FROM
      @xml.nodes('/AdminFile/Database/PO/Table_PO_Details/Data')AS TEMPTABLE(PO_Details)
End

Upvotes: 3

Related Questions