SqlCommand.ExecuteXmlReader() - How to Use?

[Steve Harclerode]

I tried this on another newsgroup but no nibbles -- I'm hoping someone might
be able to help on this csharp group --

I'm trying to use SqlCommand.ExecuteXmlReader() to write data to an XML
file. Most of the code is below. The result I'm seeing is that the schema
file is written and looks good, but the XML file has no data other than an
empty <root> node. Is there something simple I'm missing? BTW, running the
same query in query analyzer returns 7 nodes.

Thanks --
Steve

--------------------

string cmdString = "SELECT * FROM TestTable order by column1 FOR XML
AUTO, XMLDATA";
DataSet ds = new DataSet();

using ( SqlConnection sc = new SqlConnection( connectionString ) )
{
sc.Open();
SqlCommand cmd = new SqlCommand( cmdString, sc );

XmlReader xr = cmd.ExecuteXmlReader();
ds.ReadXmlSchema( xr );
ds.ReadXml( xr, XmlReadMode.Fragment );
sc.Close();
}

ds.DataSetName = "root";
ds.WriteXml( @"C:\Test.xml" );
ds.WriteXmlSchema( @"C:\Test.xsd" );

 

[Dan Bass]

An educated guess, but what is probably happening is the first node only is
being read into the data set on ReadXml...

I'd suggest using an XmlDocument object instead for what you're doing:
see this MSDN help: http://tinyurl.com/7pbjm

 

[Steve Harclerode]

Thanks for the reply.

Unfortunately, after studying the XmlDocument class definition for a while I
don't see how it could help me. I can get that it's an abstraction of an Xml
document, but I don't see what I would need to 1) read the xml 2) write the
document, and 3) write the schema. Actually, if I could do 1), probably 2)
would work, but #3) I don't see any support for in this class. Anyway, below
is the code I have now. It gives an exception on xd.Load() :
"This document already has a DocumentElement node."
Which presumably happens when it discovers that SQL is yielding XML
fragments with no root node.

----------

string cmdString = "SELECT * FROM TestTable order by column1 FOR XML AUTO,
XMLDATA";
DataSet ds = new DataSet();
XmlDocument xd = new XmlDocument();
using ( SqlConnection sc = new SqlConnection( connectionString ) )
{
sc.Open();
SqlCommand cmd = new SqlCommand( cmdString, sc );
xd.Load( cmd.ExecuteXmlReader() );
}
XmlWriter xw = new XmlTextWriter( new StreamWriter( @"C:\Test.xml" ) );
xd.WriteTo( xw );

Thanks again...

- Steve

 

[Steve Harclerode]

Okay, here's some code that fulfills 1) and 2) below. It uses the
Microsoft.Data.SqlXml class, which I had to download from "you know where".
However the output file (test.xml) also contains the schema, and I would
prefer that the schema be written to a separate file. Any suggestions?

SqlXmlCommand comm = new SqlXmlCommand( connectionString );
comm.CommandType = SqlXmlCommandType.Sql;
comm.CommandText = "SELECT * FROM TestTable order by column1 FOR XML AUTO,
XMLDATA";
comm.OutputEncoding = "UTF-8";
comm.RootTag = "root";
FileStream xmlFile = new FileStream(@"c:\test.xml", FileMode.Create,
FileAccess.Write);
comm.ExecuteToStream(xmlFile);
xmlFile.Close();

 

[Steve Harclerode]

I finally see how to write the schema to a separate file, by creating a
DataSet from the XML file using .ReadXmlSchema(), and then writing to an XSD
file using .WriteXmlSchema().

If there's a way to do this with an XmlDocument, I would be interested. So
far I have not identified any good reasons to use an XmlDocument.

Thanks, Steve