Looking for help regarding multilevel xml document to store in sql server

Question

I have the following nested level XML file which I want to store in sql2005 database by C# :

 

 

<?xml version="1.0" encoding="UTF-8"?>

<inventory>

    <id>379x573t8756k8065</id>

    <auction_id>888888</auction_id>

    <lot>

        <lot_id>1</lot_id>

        <auction_type>One Lot</auction_type>

        <bid_increment>25.0</bid_increment>

        <quantity>1</quantity>

        <title>

        <![CDATA[

        Lot 1

        ]]>

        </title>

        <description>

        <![CDATA[

        LOT LOT 1

        ]]>

        </description>

        <title_html>

        <![CDATA[

        Lot 1

        ]]>

        </title_html>

        <description_html>

        <![CDATA[

        LOT LOT 1

        ]]>

        </description_html>

        <status></status>

        <high_bidder></high_bidder>

        <images></images>

    </lot>

    <lot>

        <lot_id>2</lot_id>

        <auction_type>One Lot</auction_type>

        <bid_increment>25.0</bid_increment>

        <quantity>1</quantity>

        <title>

        <![CDATA[

        Lot 2

        ]]>

        </title>

        <description>

        <![CDATA[

        LOT LOT 2

        ]]>

        </description>

        <title_html>

        <![CDATA[

        Lot 2

        ]]>

        </title_html>

        <description_html>

        <![CDATA[

        LOT LOT 2

        ]]>

        </description_html>

        <status></status>

        <high_bidder></high_bidder>

        <images></images>

    </lot>

</inventory>

 

I wrote the code like this:

 

 

using (SqlConnection conn = new SqlConnection(connectionInfo))

            {

                // Try with sql bulk copy

 

                DataSet ds = new DataSet();

 

                StringBuilder s = new StringBuilder(); 

              s.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");

              s.Append("<inventory>\n");    

              s.Append("<id>379x573t8756k8065</id>\n");

              s.Append("<auction_id>888888</auction_id>\n");

              s.Append("<lot>\n");

               s.Append("<lot_id>1</lot_id>\n");

              s.Append("<auction_type>One Lot</auction_type>\n");

              s.Append("<bid_increment>25.0</bid_increment>\n");

              s.Append("<quantity>1</quantity>\n");

              s.Append("<title>\n");

               s.Append("<![CDATA[ Lot 1    ]]>\n");

                s.Append(" </title>\n");

                s.Append("<description>\n");

               s.Append("<![CDATA[ Lot 1    ]]>\n");

                s.Append(" </description>\n");

                s.Append("<titlexml>\n");

               s.Append("<![CDATA[ Lot 1    ]]>\n");

                s.Append(" </titlexml>\n");

                s.Append("<description_html>\n");

               s.Append("<![CDATA[ Lot 1    ]]>\n");

                s.Append(" </description_html>\n");

                 s.Append(" <status></status>\n");

 

              s.Append("</lot>");

              s.Append("</inventory>"); 

               

              String xmlString = s.ToString();

 

              // Create an XmlReader

              XmlReader reader = XmlReader.Create(new StringReader(xmlString));

 

              ds.ReadXml(reader);

 

            BUT it is filling only top 3 level node of data ID , AUCTION_ID , INVENTORY_ID …not the nested level data

 

 

SqlConnection connection = new SqlConnection(connectionInfo);

                SqlBulkCopy sbc = new SqlBulkCopy(connection);

                sbc.DestinationTableName = "BidInventory";

 

connection.Open();

 

                //table 0 is the main table in this dataset

                sbc.WriteToServer(ds.Tables[0]);

                connection.Close();

 

I tried to pass the file with full path like ds.ReadXml(("C:\\AuctionNetwork\\Message.xml");

 

But the result is the same…

 

Can you please guide me how can I the whole xml data in my dataset or is there any other way of doing it.

 

 

Please help