Keith,

SqlXmlCommand lives in the Microsoft.Data.SqlXml; namespace.
You need to install Microsoft SQLXML 3.0 Service Pack 2. Which you will find at:-
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/sqlserver.asp

I don't know how you want to measure efficiency - can be a bit of an interesting problem in an n-tier system.
My preferred route is to ship XML to the client and do as much mangling as sensible their.
This gets complicated with paging and caching strategies of course (!)

I've yet to extend my performance measurements to really come to a solid conclusion about the SQLXML library.
Following my own metrics I want to create XML as early as possible, but process as close to client as possible.
In fact the operation in the earlier post could be a candidate for migration to the client, rather than in the .Net middle tier - But I wanted to learn about the native XML handling.

Stephen
 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

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

 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