SQLXML Managed Classes allow you to author .NET code that takes advantage of the XML features provided by SQLXML 3.0. You can write managed C# or VB.NET code that utilizes FOR XML, XML templates, annotated schemas, and DiffGrams. This article, first in a series here at eggheadcafe.com , will explain the mechanics of using SQLXML Managed Classes for returning XML from SQL Server. Subsequent articles will investigate other features of SQLXml including how to set up SQLXml Webservices in IIS without writing any code by using SQL Server Virtual roots.

Three primary classes are used to access the XML functionality of SQL Server. These are:

• SqlXmlCommand. Used to send a Transact-SQL statement to the database, execute a stored procedure, or query the database using other technologies (such as annotated schemas and XML templates) and get the results back as XML. This object supports a wide range of options, which are discussed in much greater detail in the available documentation.

• SqlXmlParameter. Used to specify the value for a parameter in the command. This can be a parameter to an ad hoc query, stored procedure, XPath query, or XML template.

• SqlXmlAdapter. Used to populate a DataSet object with an XML result set, or update the database with an XML DiffGram.

In this article, we'll take a look at the SqlXmlCommand class.

The SqlXmlCommand class is the primary class used to retrieve data from SQL Server in XML format. This allows you to send queries to the database and retrieve the results as a stream or XmlReader object, or to send the output directly into another stream. The query can be parameterized, and with the help of the SqlXmlParameter class, you can specify the values for these parameters. You can execute queries using ad hoc Transact-SQL statements, stored procedures, annotated schemas and XPath, and templates. You can have the XML returned directly from the database, or you can perform the conversion to XML on the client side by simply setting a property. You can also have the SqlXmlCommand object automatically apply a style sheet to the XML result set, performing any required transformation.

To use the SqlXmlCommand class, you need to have installed SQLXML 3.0 SP1. This is available at the MSDN site. Additionally, to use SQLXml for IIS - hosted webservices (except on Windows XP which has native support) you'll need to install the SOAP Toolkit (version 2 or higher).

When authoring managed code with SqlXml classes, you'll need to place the following "using" directive at the top of your C# class:             using Microsoft.Data.SqlXml;

You'll also need to set a reference to the Microsoft.Data.SqlXml assembly in your Project /References Wizard.

Typically when an XML result set is returned from SQL Server 2000, it is an XML fragment, lacking a single root tag. Therefore, it is important to set the RootTag property of SqlXmlCommand so that the resulting XML is well formed, as follows:

cmd.RootTag="EmployeeList";

SqlXmlCommand offers several methods:

ExecuteToStream sends the resulting XML directly to a FileStream object, a NetworkStream object, a MemoryStream, or, in the case of ASP.NET, the Response object, which accepts streams.

ExecuteXmlReader returns an XmlReader object. This is a high-performance object that lets you iterate through the nodes in the XML result set. If you need random access to the XML results, you can pass XmlReader as an argument to the constructor of an XmlDocument object.

You can use ad-hoc SQL statements or you can also execute stored procedures that return XML. Consider the following stored procedure:

CREATE PROCEDURE dbo.GetEmployeesXml
AS
SELECT * FROM employees FOR XML AUTO
RETURN

This can be executed as follows:

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "Employees";
cmd.CommandText = "EXEC GetEmployeesXML";
XmlReader xr = cmd.ExecuteXmlReader();

In many cases, you cannot edit existing stored procedures to return an XML result set. In addition, there are many circumstances where, for performance or other reasons, you want to have the XML generated on the client rather than directly from the database. For these scenarios, you can use the ClientSideXml property of the SqlXmlCommand object

SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.RootTag = "EmployeeList";
cmd.ClientSideXml = true;
cmd.CommandText = "EXEC GetEmployees FOR XML NESTED";
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Response .Write(xd.OuterXml);

Generating XML on the client side involves two steps:

• You must set the ClientSideXml property of the SqlXmlCommand object to true.
• The CommandText property must contain either "FOR XML NESTED" or "FOR XML RAW" or "FOR XML Explicit". This clause is intercepted by the OLE DB provider, and indicates that the result set should be converted to XML.
Even though the stored procedure has not been modified, you will end up with an XmlReader object on the client.

If you want to change the format of the returned XML, you can use an annotated schema to define which columns will be expressed as elements, and which columns will be expressed as attributes. Annotated schemas let you further modify the result set using standard XPath. An annotated schema is an XML schema document that specifies both the tables and columns that you wish to query, and the structure of the resulting XML. SQLXML 3.0 fully supports the current W3 XML Schema Definition (XSD).

SQLXML supports exposing SQL Server 2000 directly to the Web using template files that define the query and parameters. These are XML files that are placed in a virtual root and are accessible through HTTP. You can, however, execute these template files directly using SQLXML Managed Classes. You set the CommandText property of the SqlXmlConnection to contain the path to the XML template file, and then you set the CommandType property to "SqlXmlCommandType.TemplateFile". The help file that comes with the release has examples of how to do this.

Now that we've covered a broad overview with some specific details about the SqlXmlCommand class, lets' put together a sample ASPX page with codebehind that will execute an ad - hoc query against the ever-popular Northwind database, and return same as a string of Xml to display in a web page:

Your WebForm1.aspx page should have only the one following line:

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="SQLXMLCmd.WebForm1" %>

The WebForm1.aspx.cs codebehind page is as follows:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Microsoft.Data.SqlXml;
using System.IO;
using System.Xml;

namespace SQLXMLCmd
{
/// <summary>
/// Output SQLXml Retrived from SqlXmlCommand object
/// </summary>
public class WebForm1 : System.Web.UI.Page
{

// Note the provider string - this is NOT SqlClient - its OLEDB with COM Interop --
static string NorthwindConnString = "Provider=SQLOLEDB;Server=(local);database=Northwind;" +
"Integrated Security=SSPI";

private void Page_Load(object sender, System.EventArgs e)
{
SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.RootTag="EmployeeList";
cmd.CommandText= "SELECT * FROM employees FOR XML AUTO,ELEMENTS";
XmlReader xr = cmd.ExecuteXmlReader();
xr.MoveToContent();
Response.ContentType ="text/xml";
Response.Write(xr.ReadOuterXml());
}

}
}

-- I've left out the webform designer generated code for simplicity. You can download the full Visual Studio .NET C# solution at the link below.

That's all it takes to return Xml directly out of SQL Server into either a stream or an XmlReader using the SQLXML 3.0 managed classes. Next time we'll take a look at using SQLXML to set up Webservices on IIS.

:

Download the source code that accompanies this article