[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

How to save XML data in database

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
mohantyd
Asked:
mohantyd
1 Solution
 
Anurag ThakurCommented:
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
 
mohantydAuthor Commented:
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
 
mohantydAuthor Commented:
There is no RULE_ID .. please read all Rule_ID as PARTS_ID
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Anurag ThakurCommented:
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
 
urir10Commented:
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
 
HarryNSCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now