Solved

How to save XML data in database

Posted on 2009-05-07
6
264 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run time Error 4 48
Error in script 11 57
Why does my excel data start at B1 instead of A1. 9 84
VB: Convert 2 dates to specific format 24 48
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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