Keith,
The SqlXmlCommand is part of the SQLXML web release for SQL Server [0], but it sounds like you don't need it for your specific purposes.
You can accomplish what you need either with SqlXml or just with the SqlClientCommand's ExecuteXmlReader. You need to write all the tables to the same string or stream and the read the contents of the stream into the XPathDocument.
The code will go something like this:
using System;
using System.IO;
using System.Xml;
StringWriter sw = new StringWriter();
XmlTextWriter tw = new XmlTextWriter( sw );
tw.WriteStartElement( "MyRoot" ); // create a common root for all the tables
myConnection.Open();
XmlReader reader = new SqlCommand("SELECT * from table1 FOR XML AUTO, myConnection).ExecuteXmlReader();
tw.WriteNode( reader ) );
reader.Close();
reader = new SqlCommand("SELECT * from table2 FOR XML AUTO, myConnection).ExecuteXmlReader();
tw.WriteNode( reader ) );
reader.Close();
// Always close the XmlReader when finished.
tw.EndElement(); // close MyRoot
myConnection.Close();
XPathDocument doc = XPathDocument( new StringReader( sw.ToString() ) ); // read the Xml Content from the string
// Do your stuff
The SQLXML code could be more concise and offer you more flexibility, since you could combine and externalize all the SQL statements into a SQLXML template.
The code then would be something like this:
SqlXmlCommand cmd = new SqlXmlCommand( sqlxmlConnectionString );
cmd.CommandType = SqlXmlCommandType.TemplateFile
cmd.CommandText = "mytemplatefile.xml";
// You can use ExecuteToStream if that suits you better
tw.WriteStartElement( "MyRoot" ); // create a common root for all the tables
XmlReader reader = cmd.ExecuteXmlReader();
tw.WriteNode( reader ) );
reader.Close();
For more information and sample code on SQLXML and .NET you can take a look at [1].
It's hard to to say which one's better without knowing more about what other factors come into play
--
HTH
Christoph Schittko [MVP. XmlInsider]
Software Architect, .NET Mentor
[0]
http://msdn.microsoft.com/nhp/defaul...300&frame=true
[1]
http://www.topxml.com/sqlxml/default.asp
"Keith Chadwick" <kc*******@leewardsystems.com> wrote in message news:eJ**************@TK2MSFTNGP12.phx.gbl...
I noticed an earlier post, Multiple External References in Transform - Background to the problem - Long Post, where the poster referenced an object entitled SqlXmlCommand. I am unable to find this object?
My requirement is thus. Read several tables from sql server 2000 db with the auto xml feature and read them into a single XPathDocument. Each of the tables need to be read as seperate calls and placed in a single XPathDocument. The db fetch can be either a select or stored procedure call.
What is the most efficient way to do this? Please provide an example.
Thanks
Keith
