Using SQLXML 3.0 & the Managed Classes

SQLXML 3.0 provides a very useful object library designed to take advantage of the XML features in SQL Server. The Library in question 'SqlXml' is to be found in the Microsoft.Data namespace and is typically installed with a default path of C:\Program Files\SQLXML 3.0\bin\Microsoft.Data.SqlXml.dll when you install the SQLXML 3.0 msi install. With this library it is possible to write managed code that leverages the TSQL "http://www.microsoft.com/mspress/books/sampchap/5178a.asp" target=_blank>FOR XML directive, XML Templates, UpdateGrams and Annotated Schemas. Amongst the managed classes are the SqlXmlCommand and the SqlXmlAdapter both of which will be used often throughout the article. The demo project project also highlights ways to use the FOR XML directive and SQLXML Templates using traditional ADO.NET objects. The discussion within this article is limited to the SQLXML managed classes however the code contained in the Demo application outlines alternate modes of retrieving data serialised as XML using the standard ADO.Net objects. Each example block of code will demonstrate a method of retrieving data and the subsequent binding of data objects to firstly a variety of different Windows forms controls that derive from "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebuiwebcontrolslistcontrolclasstopic.asp" target=_blank>System.Windows.Forms.ListControl namely the ListBox and ComboBox and then finally using the  "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebuiwebcontrolsdatagridclasstopic.asp" target=_blank>System.Windows.Forms.DataGrid.

The SQLXML FOR XML Clause and Modes

The basic syntax is as follows:

FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

The Arguments

The FOR XML mode sets the XML mode that will be used in determining the xml outputs structure for the query. It determines the resulting XML format. Modes are "http://www.microsoft.com/mspress/books/sampchap/5178b.asp#102" target=_blank>RAW, "http://www.microsoft.com/mspress/books/sampchap/5178c.asp#108" target=_blank>AUTO , or "http://www.microsoft.com/mspress/books/sampchap/5178d.asp#116" target=_blank>EXPLICIT.

XMLDATA

Instructs SQL Server to return an XML-Data schema which is included in the document inline.

ELEMENTS

Ensures that columns named in the query are returned as subelements and not attributes (which is the default). This argument is available only in Auto mode.

BINARY BASE64

Represents binary data in base64-encoded format.

For XML RAW

Raw mode returns xml that maps rows to xml elements and columns to element attributes. Attributes take the names of the columns listed in the sql queries select list unless aliased with another name.

Code Listing 1.0

SELECT 
      au_id as AuthorId, 
      au_fname as FirstName, 
      au_lname as LastName 
FROM
      authors 
FOR
     XML RAW

The resulting xml output looks like this:

Exhibit 1.0

<row AuthorId="409-56-7008" FirstName="Abraham" LastName="Bennet"/>
<row AuthorId="648-92-1872" FirstName="Reginald" LastName="Blotchet-Halls"/>
<row AuthorId="238-95-7766" FirstName="Cheryl" LastName="Carson"/>
<row AuthorId="722-51-5454" FirstName="Michel" LastName="DeFrance"/>
<row AuthorId="712-45-1867" FirstName="Innes" LastName="del Castillo"/>
<row AuthorId="427-17-2319" FirstName="Ann" LastName="Dull"/>
<row AuthorId="213-46-8915" FirstName="Marjorie" LastName="Green"/>
<row AuthorId="527-72-3246" FirstName="Morningstar" LastName="Greene"/>
<row AuthorId="472-27-2349" FirstName="Burt" LastName="Gringlesby"/>
<row AuthorId="846-92-7186" FirstName="Sheryl" LastName="Hunter"/>
<row AuthorId="756-30-7391" FirstName="Livia" LastName="Karsen"/>
<row AuthorId="486-29-1786" FirstName="Charlene" LastName="Locksley"/>
<row AuthorId="724-80-9391" FirstName="Stearns" LastName="MacFeather"/>
<row AuthorId="893-72-1158" FirstName="Heather" LastName="McBadden"/>
<row AuthorId="267-41-2394" FirstName="Michael" LastName="O&apos;Leary"/>
<row AuthorId="807-91-6654" FirstName="Sylvia" LastName="Panteley"/>
<row AuthorId="998-72-3567" FirstName="Albert" LastName="Ringer"/>
<row AuthorId="899-46-2035" FirstName="Anne" LastName="Ringer"/>
<row AuthorId="341-22-1782" FirstName="Meander" LastName="Smith"/>
<row AuthorId="274-80-9391" FirstName="Dean" LastName="Straight"/>
<row AuthorId="724-08-9931" FirstName="Dirk" LastName="Stringer"/>
<row AuthorId="172-32-1176" FirstName="Johnson" LastName="White"/>
<row AuthorId="672-71-3249" FirstName="Akiko" LastName="Yokomoto"/>

The example output highlights the usage of alias names for each column in the query select list. By simply adding the argument XMLDATA to the end of the query it is possible to include a schema definition that matches the XML document output.

SELECT
      au_id as AuthorId, 
      au_fname as FirstName, 
      au_lname as LastName 
FROM
      authors 
FOR
     XML RAW, XMLDATA 

For XML AUTO

The AUTO mode will map the tables listed in the query as elements and nest them according to the relationship of the tables. For example if you were to join two tables on a key value, the returned xml will nest the elements (mapped to a table) from the right side of the join. This is highlighted in the code in listing 2.0 and the resulting ouput in exhibit 2.0. Column values are returned as attributes to the element that corresponds to the element representing the table named in the query. Alias names for both columns and tables will be used in place of the database table and column names if they are specified.

Code Listing 2.0

SELECT
           author.au_fname, 
           author.au_lname, 
           titles.title_id 
FROM
          authors as author 
INNER JOIN 
          titleauthor as titles 
ON
         author.au_id = titles.au_id 
WHERE
         author.au_lname like 'B%' 
FOR
         XML AUTO 

Exhibit 2.0

<author au_fname="Abraham" au_lname="Bennet">
  <titles title_id="BU1032"/>
</author>
<author au_fname="Reginald" au_lname="Blotchet-Halls">
  <titles title_id="TC4203"/>
</author> 

By using the optional argument ELEMENTS it is possible to have the column values mapped to elements which will be returned nested inside their corresponding tables element. AUTO mode is somewhat more flexible than RAW mode in building  hierarchical output and defining data as an attribute or an element. RAW mode on the other hand is limited to repeating rows structure as  demonstrated above and this often leads to repeating data.

The SQLXML Managed Classes [The main players]

SqlXmlCommand

The SqlXmlCommand class is the SQLXML Managed version of the ADO.NET providers Command classes. It does not however share in common with it's ADO.NET counterparts the derivation from the interface IDbCommand, it inherits directly from the lowest common denominator, namely the object. Despite the apparent curiosity regarding it's object parentage the SqlXmlCommand does provide for running queries against a SQL Server Database and has the ability to return data as a stream or XmlReader. Queries defined inline or in stored procedures can be used along with SQLXML templates, Xpath and annotated schemas. It is also possible to apply xsl stylesheets directly to the SqlXmlCommand object to perform transformations and the SqlXmlCommand can also be used in conjunction with the SqlXmlParameter class in much the same way as with the ADO.NET classes.

Code Listing 4.0

This code snippet outlines an example of using the SqlXmlCommand and returning a XmlReader using it's ExecuteXmlReader method. The code is taken from the Demo Application (see the SQLXMLAUTO Button) and uses the FOR XML AUTO mode.

VB.NET

Dim coString As String = "Provider=sqloledb;data" & _ 
"source= diablo;userid=sa;password=mypassword;initial catalog=pubs" 
Dim co As New SqlXmlCommand(coString) 
Dim xmlRead As XmlReader 
Dim ds As New DataSet()
co.RootTag = "Authors" 
co.CommandType = SqlXmlCommandType.Sql 
co.CommandText = "select * from authors for xml Auto" 
xmlRead = co.ExecuteXmlReader 
ds.ReadXml(xmlRead) 
Me.DataGrid1.DataSource = ds.Tables(0) 

C#

string coString = "Provider=sqloledb;data source="+
"diablo;userid=sa;password=mypassword;initial catalog=pubs"; 
SqlXmlCommand co = new SqlXmlCommand(coString); 
XmlReader xmlRead = new XmlReader(); 
DataSet ds = new DataSet(); 
co.RootTag = "Authors"; 
co.CommandType = SqlXmlCommandType.Sql; 
co.CommandText = "select * from authors for xml Auto"; 
xmlRead = co.ExecuteXmlReader; 
ds.ReadXml(xmlRead); 
this.DataGrid1.DataSource = ds.Tables[0]; 

Harking Back

In the code above you will notice the RootTag property of the SqlXmlCommand class, which is responsible for creating a well formed XML document  by placing the tag <Authors>......</Authors> around the incomplete xml returned by the query.

Two more interesting methods of the SqlXmlCommand class are ExecuteToStream and ExecuteStream. ExecuteStream will return the xml data as a stream and ExecuteToStream offers the ability to send the xml returned by the query straight to a "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemIOFileStreamClassTopic.asp" target=_blank>FileStream for persistence to disk or a "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemnetsocketsnetworkstreamclasstopic.asp" target=_blank>NetworkStream.

Client Side and Legacy Applications

The ClientSideXml property of SqlXmlCommand class is particularly useful in cases where the developer is faced with the prospect of developing with legacy code such as stored procedures that that cannot be changed and do not use any of the xml modes to return xml output or the applications architecture demands distributed processing to be performed client side.

Code Listing 5.0

VB.NET

Dim coString As String="Provider=sqloledb;data"& _
"source=diablo;userid=sa;password=mypassword;initial catalog=pubs" 
Dim cmd As New sqlxmlCommand(coString) 
Dim xr As XmlReader 
Dim xDoc As New XmlDocument() 
Dim ds As New DataSet() 
cmd.RootTag = "Authors" 
cmd.ClientSideXml = True 
cmd.CommandText = "exec spAllAuthors for XML nested" 
xr = cmd.ExecuteXmlReader() 
xDoc.Load(xr) 
xDoc.Save("c:\testClientside.xml") ' save the output to a file 

ds.ReadXml("c:\testClientside.xml")  'read the file into a dataset 

Me.DataGrid1.DataSource = ds  'bind the datagrid to the dataset

C#

string coString = "Provider=sqloledb;data source="+
"diablo;userid=sa;password=mypassword;initial catalog=pubs" 
SqlXmlCommand cmd = new SqlXmlCommand(coString); 
XmlReader xr; 
XmlDocument xDoc = new XmlDocument(); 
DataSet ds = new DataSet(); 
cmd.RootTag = "Authors" ; 
cmd.ClientSideXml = True; 
cmd.CommandText = "exec spAllAuthors for XML nested"; 
xr = cmd.ExecuteXmlReader(); 
xDoc.Load(xr); 
xDoc.Save("c:\testClientside.xml"); //save the output to a file 

ds.ReadXml("c:\testClientside.xml");  //read the file into a dataset 

this.DataGrid1.DataSource = ds; //bind the datagrid to the dataset 

The saving of the output to a file which is consequently read into a DataSet is of no significance other than to demonstrate the possibility and of course it would not be required to save the xml returned from the query to a file in order to use it.

SqlXmlAdapter

The SqlXmlAdapter class is useful in populating DataSets and making changes to the database by way of XML Diffgram . Just as with a regular DataAdapter the SqlXmlDataAdapter has a Fill method which will populate a DataSet which is set againt the method as an argument. The code in listing 6.0 demonstrates how to use the SqlXmlDataAdapter.

Code Listing 6.0

VB.NET

Dim coString as String
coString = "Provider=sqloledb;data" & _ 
"source=diablo;userid=sa;password=mypassword;initial" & _
"catalog=pubs"
Dim cmd As NewSqlXmlCommand(coString)
cmd.RootTag = "Root" 
cmd.CommandText = "Authors[@au_lname = ""Stringer""]" 
cmd.CommandType = SqlXmlCommandType.XPath 
cmd.SchemaPath = "..\AuthorsTitles.xsd" 
Dim ds As New DataSet() 
Dim ad As New SqlXmlAdapter(cmd) 
ad.Fill(ds) 
Me.DataGrid1.DataSource = ds

C#

string coString = "Provider=sqloledb;data source=diablo;" +
"userid=sa;password=mypassword;initial catalog=pubs"
SqlXmlCommand cmd  = new SqlXmlCommand(coString);
cmd.RootTag = "Root"; 
cmd.CommandText = @"Authors[@au_lname = "Stringer"]"; 
cmd.CommandType = SqlXmlCommandType.XPath; 
cmd.SchemaPath = "..\AuthorsTitles.xsd";  
DataSet ds  = new DataSet();  
SqlXmlAdapter ad  = new SqlXmlAdapter(cmd); 
ad.Fill(ds); 
this.DataGrid1.DataSource = ds;

The above code snippet makes use of an Annotated Schema and an XPath query to return the data from the database and specify a subset (even though here we asked for everything) of the data where a condition is met. The annotated schema code that makes this possible is exhibited below.

Exhibit 5.0

It is important to note on line 3 the declaration of the sql namespace without which this example would not work and be aware that if you create a schema in Visual Studio by dragging a Table from the database onto a new xsd document in your project then line 3 will not be created by VS.NET automatically. If we look at the element 'Authors' declared above this will map directly to the authors table in the pubs database which has been specified as the catalog of the connection string passed as an argument to the instantiated SqlXmlCommand's constructor. Also worth noting is the SqlXmlCommand's CommandText property has been used to specify the XPath query.

SQLXML Templates

SQLXML Templates are files that consist of XML, SQL Statements and Parameter declarations. Template files are useful in opening access to the database over http and offer more security than queries executed at the URL. If you are interested in configuring IIS support for SQL Server please take a look at my previous article on the IE Web Controls Treeview and SQLXML. Before we go any further lets look at one.

Exhibit 6.0

The root node must include the reference the namespace above and a <sql:query> element must be nested inside the Node that will act the Document root node. Once this template file has been placed in a virtual directory configured with MMC snap in that comes with the SQLXML 3.0 install, you are free to call this template from the URL like this http://localhost/TreeSqlXml/Template/sqltemplate.xml. I am not going into setting up SQLXML Virtual Directories here, as I stated previously check out the link to my previous article which also includes some worth while links to some other quite useful tutorials on configuring and using SQLXML. Please NOTE that the 'Template' Buttons code in the Demo Application will fail if you don't configure SQLXML 3.0 support for IIS correctly and use the provided sql script "Build Database.sql" in the download. Making use of the template file using the Managed Classes turns out to be quite simple as outlined here in listing 8.0.

Code Listing 7.0

VB.NET

Dim xmlPath as String
xmlPath = "http://localhost/Treesqlxml/template/sqlTemplate.xml"
Dim ds as New  DataSet()
ds.ReadXml(xmlPath , XmlReadMode.InferSchema)

C#

string xmlPath = @"http://localhost/Treesqlxml/template/template.sql";
DataSet ds = new  DataSet();
ds.ReadXml(xmlPath, XmlReadMode.InferSchema);

If you do happen to set up a template file here is what comes back from calling it via the URL.

SqlXmlParameter

The SqlXml Managed Classes (like their ADO.Net counterparts) provide a Parameter class namely SqlXmlParameter. This class is used widely to pass values to stored procedures and inline sql queries where the query is required to behave in a dynamic manner. The code listing below assumes the xAdp (SqlXmlDataAdapter)  and xDs (DataSet) variables are declared with scope to the entire class.

Code Listing 9.0

VB.NET

Dim coString as String
coString = "Provider=sqloledb;datasource=tpol;" & _
"userid=sa;password=mypassword;initial catalog=pubs"
Dim cmd As New SqlXmlCommand(coString) 
Dim param As SqlXmlParameter 
xAdp = New SqlXmlAdapter(cmd) 
xDs = New DataSet() 
cmd.RootTag = "Authors" 
cmd.CommandType = SqlXmlCommandType.Sql 
cmd.CommandText = "select * from authors where au_lname = ? for xml auto" 
param = cmd.CreateParameter 
param.Value = Me.ListBox1.SelectedItem 
xAdp.Fill(xDs) 
Me.DataGrid1.DataSource = xDs 

C#

string coString = "Provider=sqloledb;;datasource=tpol;" +
"userid=sa;password=mypassword;initial catalog=pubs"
SqlXmlCommand cmd = new SqlXmlCommand(coString); 
SqlXmlParameter param; 
xAdp = New SqlXmlAdapter(cmd); 
xDs = New DataSet(); 
cmd.RootTag = "Authors"; 
cmd.CommandType = SqlXmlCommandType.Sql; 
cmd.CommandText = "select * from authors where au_lname = ? for xml auto"; 
param = cmd.CreateParameter; 
param.Value = this.ListBox1.SelectedItem; 
xAdp.Fill(xDs); 
this.DataGrid1.DataSource = xDs;