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