Ashish Patel

Ashish Patel

  • NA
  • 1
  • 1.4k

Read specific tags from XML nodes with varying no of tags and save to SQL DB using C# and Win form app

Sep 22 2012 5:52 AM
I am having a XML file as:

     <?xml version="1.0" encoding="UTF-8"?>
      <Bom>
            <DepthOfProdStructure>0</DepthOfProdStructure>
            <ProductIndicator>BG</ProductIndicator>
            <MaterialNumber>0843200054</MaterialNumber>
            <MaterialDescription>Andon system for NHP Assembly lines</MaterialDescription>
            <ValidFromDate>2009-01-21</ValidFromDate>
            <ValidToDate>9999-12-31</ValidToDate>
            <MeasureUnitCode>ST</MeasureUnitCode>
         </Bom>
         <Bom>
            <DepthOfProdStructure>.1</DepthOfProdStructure>
            <BomItemNumber>0010</BomItemNumber>
            <BomItemCategory>L</BomItemCategory>
            <ProductIndicator>TS</ProductIndicator>
            <MaterialNumber>0804752290</MaterialNumber>
            <MaterialDescription>Trolley for P.C</MaterialDescription>
            <ComponentQuantity>1.0</ComponentQuantity>
            <ValidFromDate>2009-01-21</ValidFromDate>
            <ValidToDate>9999-12-31</ValidToDate>
            <IndicatorItemRelevantToProduction>X</IndicatorItemRelevantToProduction>
            <MeasureUnitCode>ST</MeasureUnitCode>
            <BomMaterialNumber>0843200054</BomMaterialNumber>
         </Bom>
         <Bom>
            <DepthOfProdStructure>..2</DepthOfProdStructure>
            <BomItemNumber>0010</BomItemNumber>
            <BomItemCategory>L</BomItemCategory>
            <ProductIndicator>TS</ProductIndicator>
            <MaterialNumber>0804752292</MaterialNumber>
            <MaterialDescription>Top sheet</MaterialDescription>
            <BomItemText1>R.Mat.:Stainless steeel</BomItemText1>
            <BomItemText2>Size: 605 x 495 x 1mm Thick</BomItemText2>
            <ComponentQuantity>1.0</ComponentQuantity>
            <ValidFromDate>2009-01-21</ValidFromDate>
            <ValidToDate>9999-12-31</ValidToDate>
            <IndicatorItemRelevantToProduction>X</IndicatorItemRelevantToProduction>
            <MeasureUnitCode>ST</MeasureUnitCode>
            <BomMaterialNumber>0804752290</BomMaterialNumber>
            <AccountNumberOfVendorOrCreditor>0097001674</AccountNumberOfVendorOrCreditor>
         </Bom>

As you can see from the above the no of Tags are changing with changing node level.
from which I want only:

1. Depthofprodstructure
2. Bomitemnumber
3. MaterialNumber
4. MaterialDescription
5. BomItemText1
6. ComponentQuantity 
7. ValidFromDate
8. BomMaterialNumber

tags to be fetched and stored to the data base using DataSet string variables as I need further processing on the fetched value Row- wise. Currently I was using the following code:

    if (txtXML.Text != null)
            {
                xmlFile = XmlReader.Create(txtXML.Text, new XmlReaderSettings());

                ds.ReadXml(xmlFile);
            }
            else
            {
                MessageBox.Show("Please Choose a XML File First");
            }

            int i = 0;

            connection.Open();

            for (i = 0; i <= ds.Tables[3].Rows.Count - 1; i++)
            {

                Product_Name1 = ds.Tables[3].Rows[i].ItemArray[0].ToString();

but as the no of tags are varying I am facing a lot of problem to store the data.
I am very much new to XML reading and do not have idea about LINQ. So anyone who can give a good working example from the above data would be appreciated. Thanks in advance.