Solved

How to save XML data in database

Posted on 2009-05-07
6
262 Views
Last Modified: 2012-05-06
I have a XML document of this format

<?xml version="1.0" encoding="utf-8" ?>
- <RESPONSE>
  <Car_ID>1234567</Car_ID>
  <DECISION />
- <Engine ID="Engine 1">
  <PARTS_ID>111111</PARTS_ID>
  <Engine>323444</PARTS_ID>
  </Engine>
- <Engine ID="ENGINE 2">
  <RULE_ID>1222344</RULE_ID>
  <RULE_ID>2345354</RULE_ID>
  </Engine>
  <Car_ID>322445666</Car_ID>
  <DECISION />
- <Engine ID="ENGINE 3">
  <PARTS_ID>764890</PARTS_ID>
  <PARTS_ID>0123456</PARTS_ID>
  </Engine>
  </RESPONSE>

And a database table(SQL server)
CAR varchar(50)
DECISION varchar(50)
ENGINE varchar(50)
Parts_ID varchar(50)

Can any one tell me the easiest way to save the xml data in database. Please give code in C#

0
Comment
Question by:mohantyd
6 Comments
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24324417
first question do you want to save the complete xml in the database in one go or save the parts of the xml
looking at u r databae design something is incomplete here
car has 3 engines with different parts

also the fields have size of varchar 50 isnt it too small to save the complete xml - if u want to do that
0
 

Author Comment

by:mohantyd
ID: 24324677
I want to save data from xml file to database.  

CAR_ID      DECESION    ENGINE   PARTS_ID
 1234567                        Engine 1  111111
1234567                         Engine 1   323444
1234567                         Engine 2   1222344
1234567                         Engine 2   2345354
322445666                     Engine 3   764890
322445666                     Engine 3   0123456

Table should look like this if i store data in table from xml file .....

0
 

Author Comment

by:mohantyd
ID: 24324869
There is no RULE_ID .. please read all Rule_ID as PARTS_ID
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24325917
i was trrying to write the code and seems that there are some mistakes in the XML
can you please upload the correct xml
0
 
LVL 7

Expert Comment

by:urir10
ID: 24326680
I think a good way would be to do Deserialization on the xml file, load it into variables or a class that you create and then write it to the database.

Read here:
http://www.devhood.com/Tutorials/tutorial_details.aspx?tutorial_id=236
0
 
LVL 6

Accepted Solution

by:
HarryNS earned 250 total points
ID: 24327417
Try the following code. This will surely work...

There were some issues with your XML, I modified it as below and tested

<?xml version="1.0" encoding="utf-8" ?>
<RESPONSE>
      <Car_ID>1234567</Car_ID>
      <DECISION />
      <Engine ID="Engine 1">
            <PARTS_ID>111111</PARTS_ID>
            <PARTS_ID>323444</PARTS_ID>
      </Engine>
      <Engine ID="ENGINE 2">
            <PARTS_ID>1222344</PARTS_ID>
            <PARTS_ID>2345354</PARTS_ID>
      </Engine>
      <Car_ID>322445666</Car_ID>
      <DECISION />
      <Engine ID="ENGINE 3">
            <PARTS_ID>764890</PARTS_ID>
            <PARTS_ID>0123456</PARTS_ID>
      </Engine>
</RESPONSE>
private void LoadAndSaveXML()

        {

            string strCarID = string.Empty;

            string strDecision = string.Empty;

            

            XmlNode xmlNdeEngine = null;
 

            string strFile = @"C:\\Test.XML";
 

            XmlDocument xmlDoc = new XmlDocument();

            xmlDoc.Load(strFile);
 

            string strConn = "Data Source=<DB SERVER>;Initial Catalog=<DB>;Integrated Security=True";
 

            using (SqlConnection conn = new SqlConnection(strConn))

            {

                conn.Open();
 

                foreach (XmlNode xmlNde in xmlDoc.GetElementsByTagName("Car_ID"))

                {

                    strCarID = xmlNde.FirstChild.Value;

                    strDecision = xmlNde.NextSibling.Value;
 

                    xmlNdeEngine = this.SaveEngineAttribute(xmlNde.NextSibling.NextSibling, conn, strCarID, strDecision);

                    while (xmlNdeEngine != null)

                    {

                        xmlNdeEngine = this.SaveEngineAttribute(xmlNdeEngine, conn, strCarID, strDecision);

                    }

                }

            }

        }
 

        private XmlNode SaveEngineAttribute(XmlNode xmlNde, SqlConnection conn, string strCarID, string strDecision)

        {

            string strEngine = string.Empty;

            string strPartsID = string.Empty;
 

            if (xmlNde.Name == "Engine")

            {

                SqlCommand cmd = new SqlCommand();                

                strEngine = xmlNde.Attributes[0].Value;
 

                foreach (XmlNode xmlNde1 in xmlNde.ChildNodes)

                {

                    strPartsID = xmlNde1.FirstChild.Value;

                    cmd.Connection = conn;

                    cmd.CommandText = "INSERT INTO XMLData Values('" + strCarID + "','" + strDecision + "','" + strEngine + "','" +

                        strPartsID + "')";

                    cmd.ExecuteNonQuery();

                }

            }
 

            if (xmlNde.NextSibling != null && xmlNde.NextSibling.Name == "Engine")

                return xmlNde.NextSibling;

            else

                return null;

        }

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now