Solved

How to save XML data in database

Posted on 2009-05-07
6
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

726 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