Last post Nov 17, 2010 06:15 AM by svibuk
Jul 19, 2010 12:09 AM|nige_w|LINK
I am wanting to create an XML file from data within a database.
I have successfully done this using "XmlTextWriter" class.
I now have a sql query outputting performatted xml data and want to "inject" this into the XmlTextWriter rather than interate through a dataset and write the individual nodes.
My code is:
XmlTextWriter writer = new XmlTextWriter("d:/MyXMLFile.xml", Encoding.UTF8);
writer.Formatting = Formatting.Indented;
using (SqlConnection con = new SqlConnection())
con.ConnectionString = GlobalSettings.DbDSN;
SqlCommand cmd = new SqlCommand("My SQL query FOR XML EXPLICIT;", con);
XmlReader reader = cmd.ExecuteXmlReader();
XmlDataDocument xml = new XmlDataDocument();
??WHAT GOES HERE TO WRITE XML DATA TO XMLTEXTWRITER
Can anyone please advise if / how this can be done.
XML .Net C# character entities WebClient XmlDocument
Jul 19, 2010 12:40 AM|muhammedsalimp|LINK
Please have a look
XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005-Documents, Fragments and FOR XML Support
Jul 20, 2010 09:16 PM|Decker Dong - MSFT|LINK
If necessary, please use SqlDataAdapter.Fill to get a memory-based table, and store it into the DataSet, and use DataSet.WriteXml("filepath and name)";
Jul 21, 2010 04:36 AM|nige_w|LINK
Thanks for your comments.
muhammedsalimp - I have taken a look at the page suggested but so far haven't been able to figure out a solution.
Decker Dong - MSFT - this may work (haven't had time to investigate yet) however I do need to wrap the root node tags around my sql query output before saving. I guess another option might be to figure out how to write my query query to include the
required root tag in the output.
I think I need more sleep and more coffee ! I'll persevere.
Jul 21, 2010 08:03 AM|muhammedsalimp|LINK
I have taken a look at the page suggested but so far haven't been able to figure out a solution.
I was suggesting to use something like this :
XmlTextWriter writer = new XmlTextWriter("C:/MyXMLFile.xml", Encoding.UTF8);
XmlDocument Xd = new XmlDocument();
SqlConnection oSqlConnection = new
SqlCommand oSqlCommand = new SqlCommand(" SELECT * FROM dbo.Books " +
" FOR XML AUTO, ROOT('Root') ", oSqlConnection);
Check whether this works and let us know
Jul 23, 2010 12:03 AM|nige_w|LINK
thanks for the info.
Your code above was awesome - I tweaked it slightly to include the root element of my XML file and it runs fantastically fast - approx 5 secs to write a 4Mb file - wicked !
Nov 17, 2010 06:15 AM|svibuk|LINK
i tried using above code
i get incorrect syntax ear ROOT
Dim cmd As
New SqlCommand(" SELECT * FROM dbo.tbl " &
" FOR XML AUTO ", cn)