XML Generation

[Guest]

I've got a few tables that I want to select records out of and put into an XML file. Each table will have its own node tree with it's records underneath. Is there a "good" way to do this besides doing it manually (looping thru datareaders and stuff like that). I've tried using the XmlReader/DataSet.WriteXml() and only get one record. I'm using a SQL Server query with FOR XML AUTO, ELEMENTS appended to it. Any help would be greatly appreciated.

Todd

 

[Jim Douglas]

What about using the Dataset and it's methods to create the XML?

I've got a few tables that I want to select records out of and put into an

XML file. Each table will have its own node tree with it's records
underneath. Is there a "good" way to do this besides doing it manually
(looping thru datareaders and stuff like that). I've tried using the
XmlReader/DataSet.WriteXml() and only get one record. I'm using a SQL
Server query with FOR XML AUTO, ELEMENTS appended to it. Any help would be
greatly appreciated.

 

[Guest]

Have you looked at the managed SQLXML classes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_0jck.asp ?

 

[Benjamin Disraeli]

I've got a few tables that I want to select records out of and put into an
XML file. Each table will have its own node tree with it's records
underneath. Is there a "good" way to do this besides doing it manually
(looping thru datareaders and stuff like that). I've tried using the
XmlReader/DataSet.WriteXml() and only get one record. I'm using a SQL
Server query with FOR XML AUTO, ELEMENTS appended to it. Any help would
be greatly appreciated.

Todd

This is easy as pie.

Instead of doing an ExecuteReader on your SqlCommand, do an ExecuteXMLReader
and it loads it into an XML file.

XMLReader xr = myCmd.ExcecuteXMLReader();

Take the resultant ( xr ) and create an XML Document with it.

XMLDocument xd = new XMLDocument(xr);

Then do a xd.Save("<filename>") name on the Document.

 

[Guest]

Tried this one too. It only gives me one record back, unless I do a read for each record. And as far as I can tell, there's no way to tell how many records there are.

 

[Guest]

According to the docs, SQLXML is able to be used if VS .Net is installed? Yet the IDE will still not show me the System.Data.SqlXml class? Guess I'm just gonna have to do this manually.

 

[Guest]

I made a mistake... one of the examples references using Microsoft.Data.SqlXml. But intellisense doesn't show a Data class under the Microsoft class???

 

[William Ewart Gladstone]

Todd Bright wrote:

Left off a step:

http://www.dbforums.com/t698233.html

strTmp = xmlReader.ReadOuterXml()
While strTmp.Length > 0
sbBuilder.Append(strTmp)
strTmp = xmlReader.ReadOuterXml()
End While
sbBuilder.Append("")

xmlReader.Close()
xmlReturn.LoadXml(sbBuilder.ToString())

 

[Guest]

I'll have to bookmark this one.

I ended up just looping thru the recordset manually and using the XmlTextWriter object. MUCH more straightforward than the XmlReader. I spent half a day investigating different ways to execute a SQL statement back and put into an XML file, none of which worked the way I was looking for. Took me maybe an hour to investigate the XmlTextWriter and write the code to implement it.