Solved

How to save XML data in database

Posted on 2009-05-07
6
261 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

18 Experts available now in Live!

Get 1:1 Help Now