Solved

How to save XML data in database

Posted on 2009-05-07
6
265 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

830 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