Load XML file into SQL Server tables using C#

Question

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;

                                            }
                                         }
                                    }
                                }
                            }
                        }
                    }
                }
            }

Are you using any ORM or you need to insert it using store procedure? — programtreasures
– programtreasures, Commented Dec 11, 2017 at 4:01
Using store procedure and C#. I do not know how to insert that XML into 2 tables — Cát Tường Vy
– Cát Tường Vy, Commented Dec 11, 2017 at 4:03
edited. I also want to know how to export the XML file from 2 SQL Tables using C# like this XML file structure. Please help too — Cát Tường Vy
– Cát Tường Vy, Commented Dec 11, 2017 at 4:18

programtreasures · Accepted Answer · 2017-12-11 04:11:19Z

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

Daniel B · Accepted Answer · 2017-12-11 04:34:01Z

1

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

answered Dec 11, 2017 at 4:34

Daniel B

3,2152 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

Load XML file into SQL Server tables using C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related