Read\Write XML (Recurring Tags) Using VB.Net

satmisha
satmisha used Ask the Experts™
on
Hi Expert,

Need to Read XML file having Recurring tags and after reading those I need to insert them in DataBase.

Here is XML file: Here Tags are repeated:

<?xml version="1.0" encoding="utf-8" ?>
<po:PurchaseOrder xmlns:po="http://michalk.com/XmlDOM/PO.xsd"[ccc]
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"[ccc]
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Number>1001</Number>
<OrderDate>8/12/01</OrderDate>
<BillToAddress>
   <Street>101 Main Street</Street>
   <State>NC</State>
   <ZipCode>28273</ZipCode>
</BillToAddress>
<BillToAddress>
    <State>NC</State>
    <ZipCode>28273</ZipCode>
</BillToAddress>
<ShipToAddress>
   <Street>101 Main Street</Street>
   <City>Charlotte</City>
   <State>NC</State>
</ShipToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
  </ShipToAddress>

</po:PurchaseOrder>

Please suggest.

Looking forward to hearing from you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
anarki_jimbelSenior Developer

Commented:
Please give more info.

Does the xml always contain two ship-to addresses, two bill-to addresses?
Why some addresses have not all information?

What is the structure of your DB? Are there tables for addresses? Orders?

Author

Commented:
Thanks for your quick reply.

Consider it as nested XML:
# XML can contain multiple "ship-to addresses", Max would be 99.
# Similarly "two bill-to addresses" is also Dynamic.
# XML is dynamic and Nested in nature and might not contain all the Inner Nodes.
# There is only one Table Which contains all the relevent column if information is not provided like for address as you stated it will contain null for those.

Pls Assist.
anarki_jimbelSenior Developer

Commented:
"# There is only one Table Which contains all the relevent column"

Orders table contains up to 99 fields for addresses?

Anyway, let me try with some code...
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Senior Developer
Commented:
OK, here is some code.
I used simplified version of your xml as I have no xsd.
You may need to use a namespace manager when using my code with namespaced xml

I extract data from xml and then print it. You need to save data into DB.

        private void button1_Click(object sender, EventArgs e)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load("data.xml");
            const string NumberTag = "Number";
            const string OrderDateTag = "OrderDate";
            const string BillToAddressTag = "BillToAddress";
            const string StreetTag = "Street";
            const string StateTag = "State";
            const string ZipCodeTag = "ZipCode";
            const string ShipToAddressTag = "ShipToAddress";
            string orderNumber;
            DateTime orderDate;
            List<string> billToAddresses = new List<string>();
            List<string> shipToAddresses = new List<string>();

            XmlNode orderNumberNode = doc.DocumentElement.SelectSingleNode(@"./" + NumberTag);
            orderNumber = orderNumberNode.InnerText;

            XmlNode orderDateNode = doc.DocumentElement.SelectSingleNode(@"./" + OrderDateTag);
            orderDate = DateTime.Parse(orderDateNode.InnerText);

            XmlNodeList billToAddressesNodes = doc.GetElementsByTagName(BillToAddressTag);
            foreach (XmlNode node in billToAddressesNodes)
            {
                string address = "";
                XmlNode streetNode = node.SelectSingleNode(@"./" + StreetTag);
                XmlNode stateNode = node.SelectSingleNode(@"./" + StateTag);
                XmlNode zipNode = node.SelectSingleNode(@"./" + ZipCodeTag);
                address = (streetNode == null) ? address : address + streetNode.InnerText + Environment.NewLine;
                address = (stateNode == null) ? address : address + stateNode.InnerText + Environment.NewLine;
                address = (zipNode == null) ? address : address + zipNode.InnerText + Environment.NewLine;
                billToAddresses.Add(address);
            }

            XmlNodeList shipToAddressesNodes = doc.GetElementsByTagName(ShipToAddressTag);
            foreach (XmlNode node in billToAddressesNodes)
            {
                string address = "";
                XmlNode streetNode = node.SelectSingleNode(@"./" + StreetTag);
                XmlNode stateNode = node.SelectSingleNode(@"./" + StateTag);
                XmlNode zipNode = node.SelectSingleNode(@"./" + ZipCodeTag);
                address = (streetNode == null) ? address : address + streetNode.InnerText + Environment.NewLine;
                address = (stateNode == null) ? address : address + stateNode.InnerText + Environment.NewLine;
                address = (zipNode == null) ? address : address + zipNode.InnerText + Environment.NewLine;
                shipToAddresses.Add(address);
            }

            System.Diagnostics.Debug.WriteLine("==================================");
            System.Diagnostics.Debug.WriteLine("Order Number = " + orderNumber);
            System.Diagnostics.Debug.WriteLine("Order Date = " + orderDate.ToString());

            foreach (string addStr in billToAddresses)
            {
                System.Diagnostics.Debug.WriteLine("~~~~Bill to Address:");
                System.Diagnostics.Debug.WriteLine(addStr);
            }
            System.Diagnostics.Debug.WriteLine("");
            foreach (string addStr in shipToAddresses)
            {
                System.Diagnostics.Debug.WriteLine("~~~~Ship to Address:");
                System.Diagnostics.Debug.WriteLine(addStr);
            }
        }

Open in new window


Xml:

<?xml version="1.0" encoding="utf-8" ?>
<!--<po:PurchaseOrder xmlns:po="http://michalk.com/XmlDOM/PO.xsd" [ccc]
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" [ccc=""]
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">-->
<PurchaseOrder>
  <Number>1001</Number>
  <OrderDate>8/12/01</OrderDate>
  <BillToAddress>
    <Street>101 Main Street</Street>
    <State>NC</State>
    <ZipCode>28273</ZipCode>
  </BillToAddress>
  <BillToAddress>
    <State>NC</State>
    <ZipCode>28273</ZipCode>
  </BillToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
    <State>NC</State>
  </ShipToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
  </ShipToAddress>

</PurchaseOrder>

Open in new window

anarki_jimbelSenior Developer

Commented:
Output:


==================================
Order Number = 1001
Order Date = 8/12/2001 12:00:00 a.m.
~~~~Bill to Address:
101 Main Street
NC
28273

~~~~Bill to Address:
NC
28273


~~~~Ship to Address:
101 Main Street
NC
28273

~~~~Ship to Address:
NC
28273

Author

Commented:
Thanks a lot for the code. I am going to allocate full point to this.

But My requirment is to Read the Nested XML file then validate it and then Insert into the DataBase using vb.net Code. I'll be happy if you cd write some code to achieve that.

Also what wd the better approach .

Should I Deserialize the XML into object , Validate it and then Pass the Data to DataBase to get inserted into Table..?

I am going to Open new thread and seeking your help on it.
Here is the Question: Please assist.

Read Nested XML then Validate and finally Insert the Data in DataTable"

Author

Commented:
pls reply.
anarki_jimbelSenior Developer

Commented:
Sorry, we probably have some time shift. And I live under Pacific time :)

"Should I Deserialize the XML into object"
Yes, I'd use this approach. really, that's what I wanted to suggest, just tried to keep my code simpler.
So, you need a PurchaseOrder class with properties like:
- Number
- Date
- BillToAddresses
- ShipToAddresses

About validation - it might be a bit more tricky. From one hand, you have an xml schema and you may to validate against the schema.
Validation can also be done at an object level.

Saving into a database should be pretty straightforward. But to write code I need to know your DB structure.

If you are not familiar with C# DB access, try the following resource:
http://www.programmersheaven.com/2/les_csharp_13_p1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial