your question is confusing - your XML file schema is laied out in a way that it will need multiple tables to store the data, then why do you wish to store it in a single table? The only way I would suggest would be to use an XML Datatype in sql and store
the XML file as is, if its mandatory to store the xml file into a single table in the database. Although I am not sure where does SQLBulkCopy fit into this picture.
Some articles to guide you on SqlBulkCopy (it does one table at a time)
Hi, you can use the following codes to output the data of the data tables in reportData. These tables have different rows and columns, how do you want to push them into one table?
foreach (DataTable table in reportData.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (var cell in row.ItemArray)
{
Response.Write(cell.ToString() + " ");
}
Response.Write("<br />");
}
Response.Write("-----------------------------------<br />");
}
ganapati.vvs
Member
81 Points
102 Posts
SqlBulkCopy: Problem in Copy data from XML to Sql Server DB
Aug 02, 2012 01:58 PM|LINK
Hi all,
i want to do SqlBulkCopy from XML to SQl Server DB.
its working good with this code.
connection = new SqlConnection(ConnectionString); UploadingStatusLabel.Text = "Uploading...Please wait."; DataSet reportData = new DataSet(); reportData.ReadXml(Server.MapPath("~//XMLFiles//Team_Info.xml"));The reportData contains 30+ tables according to my XML file.
XML File is..
<sports-statistics> <sports-schedule> <date year="2009" month="9" date="22" day="1"/> <time hour="18" minute="50" second="58" timezone="Eastern" utc-hour="-4" utc-minute="00"/> <version number="2"/> <season season="2010"/> <football-nfl-schedule> <game-schedule> <visiting-team> <team-name name="Vikings" alias="Min"/> <team-city city="Miesota"/> <team-code id="6" global-id="47"/> </visiting-team> <home-team> <team-name name="Saints" alias="GO"/> <team-city city="New Orleans"/> <team-code id="158" global-id="3550"/> </home-team> <date year="2009" month="9" date="9" day="4"/> <time hour="20" minute="30" timezone="Eastern" utc-hour="-4" utc-minute="00"/> <local-game-date year="2010" month="9" date="9" day="4"/> <local-time hour="19" minute="320"/> <tba tba="false"/> <week week="1"/> <tv> <station call-letters="NFFC" name="NFFC"/> </tv> <gamecode code="2012121225878" global-id="975280"/> <gametype id="2" type="Regular Season"/> <stadium name="Louisiana Superdome" city="New Orleans" state="alabama" country="United States" id="18" global-id="343"/> <status id="4" status="Final"/> <visiting-team-score score="9"/> <home-team-score score="134"/> <total-quarters total="43"/> <outcome-visit outcome="Loss"/> <outcome-home outcome="Win"/> <attendance attendance="75,051"/> </game-schedule> <bye-week week="11" > </bye-week> <bye-week week="22" > </bye-week> <bye-week week="33" > </bye-week> <bye-week week="44" > <bye-team team="56"/> <bye-team team="162"/> <bye-team team="166"/> <bye-team team="297"/> </bye-week> <bye-week week="53" > <bye-team team="145"/> <bye-team team="157"/> <bye-team team="223"/> <bye-team team="216"/> </bye-week> <bye-week week="67" > <bye-team team="2"/> <bye-team team="4"/> <bye-team team="22"/> <bye-team team="29"/> </bye-week> <bye-week week="7" > <bye-team team="8"/> <bye-team team="11"/> <bye-team team="20"/> <bye-team team="374"/> </bye-week> <bye-week week="98" > <bye-team team="91"/> <bye-team team="93"/> <bye-team team="5"/> <bye-team team="519"/> <bye-team team="271"/> <bye-team team="313"/> </bye-week> <bye-week week="94" > <bye-team team="74"/> <bye-team team="10"/> <bye-team team="174"/> <bye-team team="215"/> <bye-team team="298"/> <bye-team team="300"/> </bye-week> <bye-week week="130" > <bye-team team="9"/> <bye-team team="13"/> <bye-team team="18"/> <bye-team team="24"/> </bye-week> <bye-week week="171" > </bye-week> <bye-week week="102" > </bye-week> <bye-week week="183" > </bye-week> <bye-week week="174" > </bye-week> <bye-week week="155" > </bye-week> <bye-week week="126" > </bye-week> <bye-week week="137" > </bye-week> <bye-week week="18" > <bye-team team="1"/> <bye-team team="73"/> <bye-team team="117"/> <bye-team team="283"/> </bye-week> <bye-week week="139" > </bye-week> <bye-week week="210" > </bye-week> </football-nfl-schedule> </sports-schedule> </sports-statistics>Problem is... I want to take data from multiple tables in Dataset and push up into a single table in the Database..
How can i do this..??
Please help me..
thanks in Advance...
sanddy19
Participant
1058 Points
144 Posts
Re: SqlBulkCopy: Problem in Copy data from XML to Sql Server DB
Aug 03, 2012 02:34 PM|LINK
your question is confusing - your XML file schema is laied out in a way that it will need multiple tables to store the data, then why do you wish to store it in a single table? The only way I would suggest would be to use an XML Datatype in sql and store the XML file as is, if its mandatory to store the xml file into a single table in the database. Although I am not sure where does SQLBulkCopy fit into this picture.
Some articles to guide you on SqlBulkCopy (it does one table at a time)
http://www.4guysfromrolla.com/articles/102109-1.aspx
http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy
http://msdn.microsoft.com/en-us/library/ms172141.aspx
http://msdn.microsoft.com/en-us/library/s4s223c6.aspx
Saurabh Nandu
Systenics Solutions
My Blog
Allen Li - M...
Star
10411 Points
1196 Posts
Re: SqlBulkCopy: Problem in Copy data from XML to Sql Server DB
Aug 07, 2012 06:31 AM|LINK
Hi, you can use the following codes to output the data of the data tables in reportData. These tables have different rows and columns, how do you want to push them into one table?
foreach (DataTable table in reportData.Tables) { foreach (DataRow row in table.Rows) { foreach (var cell in row.ItemArray) { Response.Write(cell.ToString() + " "); } Response.Write("<br />"); } Response.Write("-----------------------------------<br />"); }If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework