.NET SqlXmlCommand Class

The SqlXmlCommand class is your one-stop shop to access the XML features of SQL Server and SQLXML. Similar to the System.Data.SqlClient.SqlCommand class or the Command class in classic ADO, we can issue commands to the database and add parameter values for parameterized queries. With the SqlXmlCommand class the actual command can be in one of the five different SQLXML formats: SQL statements returning XML, Updategrams, DiffGrams, XPath queries and Templates. We tell the SqlXmlCommand object which one of the formats to execute by setting the CommandType property to the corresponding value from the SqlXmlCommandType enumeration before we execute the command.

We have two options to specify the command text: the CommandText or the CommandStream property. The former is of type string, the latter of type Stream. CommandText helps us to avoid any conversions or memory copies when we dynamically create a SQL query or an XPath expression with a System.Text.StringBuilder object or a call to the String.Format(). CommandStream on the other hand reads the command from Stream object, which could be a FileStream to read from a file or a MemoryStream if we compose an Updategram dynamically in memory.

Once we have set the command text and its type we can execute the command. SqlXmlCommand gives us several options to retrieve the results. We can call the ExecuteXmlReader() method to get all the results nicely packaged in an XmlReader to traverse over the results. Alternatively, SQLXML will return the results as a raw stream. If we call the ExecuteStream() method the SqlXmlCommand will return a new stream and return it to us, or, if we want to inject the results into an existing stream, we supply the target stream to the ExecuteToStream() method. The next code example shows how we set up a SqlXmlCommand to execute a SQL query returning XML and then write the results directly to the Console with the ExecuteToStream() method.

static string connString = "Provider=SQLOLEDB;Server=(local);database=XMLBookDB;";

public static void TestSqlXml ()

{

    Stream outStream = Console.OpenStandardOutput();

    SqlXmlCommand cmd = new SqlXmlCommand(connString);

    cmd.Root = "Suppliers";

    cmd.CommandType = SqlXmlCommandType.Sql;

    cmd.CommandText = "SELECT SupplierName, WebSite FROM Suppliers

        FOR XML AUTO";

    strm = cmd.ExecuteToStream( outStream );

    outStream.Close();

}

The example above also sets the Root property to instruct the SqlXmlCommand object to return well-formed XML. Without setting the Root property SQLXML would return the XML fragment returned by SQL Server without a common root node, which could cause exceptions if we processed the results with an XmlTextWriter or an XmlDocument for example. Having the SqlXmlCommand generating the root tag is not only convenient, it also avoids an extra, potentially expensive processing step if we had to add a root element around the returned results.

The SqlXmlCommand class exposes more properties we haven’t discussed yet. The SchemaPath property, for example, specifies the location of the mapping schema for an XPath query or an Updategram which we will learn how to use shortly. Another property allows us to handle the transformation of the resultset into XML on  the application server rather than using up processing cycles on SQL Server. We will discuss client-side XML generation later on. The SqlXmlCommand also does XSLT transformations as part of the command execution, but we will not examine them closer because we should use that feature only in a rather small application without well-defined application layers. Mapping schemas are sufficient to handle the initial transformation from the database schema into an XML format. The application layer should handle more complex transformations to keep the data access layer as generic as possible. Furthermore, the SqlXmlCommand class can only load XSL stylesheets from the file system, whereas the System.Xml.Xslt namespace allows processing of stylesheets from any type of stream source. Table 15.9 lists all methods and properties for quick reference purposes.

1.9            The SqlXmlCommand class executes the difference command types in SQLXML: SQL Statements returning XML, XPath queries, Query Templates, Updategrams andDiffgrams.