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" <Li*************@hot.mail.com> wrote in message
news:Ow**************@TK2MSFTNGP14.phx.gbl...

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" );

[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

"Dan Bass" <Not Listed> wrote in message
news:un**************@TK2MSFTNGP15.phx.gbl...

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]

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" <Ca***************@hot.mail.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

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.

[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

"Steve Harclerode" <Ca***************@hot.mail.com> wrote in message
news:e6**************@TK2MSFTNGP09.phx.gbl...

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();