Click here to Skip to main content
Click here to Skip to main content

Using SQLXML 3.0 & the Managed Classes

By , 4 Oct 2003
Rate this:
Please Sign up or sign in to vote.
<!-- Add the rest of your HTML here -->

Introduction

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.

For XML EXPLICIT

Although AUTO mode is indeed more flexible than RAW mode it is EXPLICIT mode that offers developers the most flexible outcomes and with that we inherit greater complexity and more often that not greater overhead. Far greater control over nesting, using CDATA sections, XML schema type ID and IDREF and column values being assigned to either elements and or attributes in a single document are just some of the possibilites with EXPLICIT mode.

EXPLICIT mode builds resultsets into a 'Universal Table' which is what you will see in query analyser (and gives some clues as to what is actually going on under the hood)  if you leave out the FOR XML EXPLICIT part of the query. Before discussing this mode any further let's look at a query:

Code Listing 3.0

SELECT 1 AS Tag, 
         0 AS Parent, 
         author.au_fname AS [Author!1!FirstName], 
         author.au_lname AS [Author!1!LastName], 
         null AS [Title!2!TitleId] 
FROM
         authors AS author 
WHERE
         author.au_lname LIKE 'B%' 
UNION
         ALL
SELECT
         2 AS Tag, 
         1 AS Parent, 
         author.au_fname, 
         author.au_lname, 
         title.title_id AS [Title!2!TitleId] 
FROM
         authors AS author 
INNER JOIN 
         titleAuthor AS Title 
ON
         author.au_id = Title.au_id 
WHERE
         author.au_lname LIKE 'B%' 
ORDER BY 
         [Author!1!FirstName], 
         [Author!1!LastName], 
         [Title!2!TitleId] 
FOR
         XML EXPLICIT

Assuming that you have the PUBS database installed, if you copy this query into query analyser and run it without the 'for xml explicit' you will have a universal table staring back at you and if you run it as is then you will get the returned xml. A small hint at this juncture: go to the tools menu in query analyser, select options in the menu and then the results tab and set the maximum characters per column to have all the xml returned to the results pane.

Column Names in the Select List and Arguments

What is that funny looking syntax used after the column names specified in the select list?

[ElementName!TagNumber!AttributeName!Directive]

The SELECT list requires some extra tweaking that we haven't yet encountered with RAW and AUTO modes. First thing worth noticing are the two columns Tag and Parent which are meta data columns which help define the nesting of xml elements in the DOM Tree. The ElementName argument specifies the name for the elements tag e.g. <Author>. The AttributeName and Directive (optional) arguments work in tandem to produce different output. AttributeName can be used to specify the name of the attribute for the specified ElementName that will contain the select lists columns value.  When AttributeName is used in conjunction with Directive it is possible to specify a contained element in the xml output. For example the following query

SELECT
        1 AS Tag, 
        null AS Parent, 
        au_fname AS [Author!1!FirstName!Element], 
        au_id AS [Author!1!ID] 
FROM
        authors 
FOR
        XML EXPLICIT

will produce the following output excerpt.

<Author ID="409-56-7008">
    <FirstName>Abraham</FirstName>
</Author>
<Author ID="648-92-1872">
    <FirstName>Reginald</FirstName>
</Author>
<Author ID="238-95-7766">
    <FirstName>Cheryl</FirstName>
</Author>
<Author ID="722-51-5454">
    <FirstName>Michel</FirstName>
</Author>
<Author ID="712-45-1867">
    <FirstName>Innes</FirstName>
</Author>
<Author ID="427-17-2319">
    <FirstName>Ann</FirstName>
</Author>

Note the nested <FirstName> element as specified by the directive [Author!1!FirstName!Element].

One thing I should mention just in case you have already tried and wondered why pasting the returned xml into an xml document doesnt work and threw this error: 'Only one top level element is allowed in an XML document. Error processing resource 'file:///etc etc'. Xml returned by the three modes do not in fact return valid xml documents because it does not supply a root node. Therefore if you try the query above and place the xml result into a file and place an outer element around it (as demonstrated in exhibit 3.0) as the root node it will work. For further reference on XML explicit see SQL Server Books On Line references or for another code example of how to use it see my previous article http://www.thecodeproject/useritems/sqlxmltreeview.asp.

Exhibit 3.0

You can also place code in the query to produce the root node by encapsulating the code in Code listing 3.0 into a stored procedure and adding two extra lines of code to the top and bottom of the stored procedure.

CREATE PROCEDURE spXMLAuthors 
AS
SELECT '<Authors>' 
-- Code from Listing 3.0 
SELECT '</Authors>'

This will return three result sets which can be used to generate a well formed XML Document. Without going into this issue any further let me just say that the SQLXML Managed Classes provide a neat way of managing this and we will discuss it again later in the article. 

SQLXML Demo Application.

Exhibit 4.0

The Demo Application includes a 'Build Stored Procedures.sql' file which demonstrates the usage of all three modes name AUTO, EXPLICIT AND RAW. It should be noted that the Demo Application is of no value in itself, it exists purely as a harness to demonstrate a variety of example code and some potential GUI usage from it.

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;

In Summary

The SqlXml 3.0 Managed Classes give .NET developers the ability to write code that leverages the SQLXML features available in SQL Server. The goal of this article is to provide a platform for beginning to use the FOR XML modes and the Managed Classes, particularly if you happen to have a lot of SQLXML code around the place would like to start using it in a .NET environment.

Final Note:

Much of the managed .NET code listed here is based around templates found in the whitepaper by Scott Swiggart (thanks Scott) which is available with the SQLXML 3.0 download. The project download is available in VB.NET and C#, however the non SqlXml 3.0 examples are only contained in the VB.NET project. Please remember to install the SqlXml 3.0 package and change all the connection strings accordingly before running the demo project otherwise it wont work. For those considering SQLXML not just from a .NET perspective and wish to start experimenting with it I can recommend the Microsoft Press book to which there are links in this article.

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Simon Segal
Chief Technology Officer
Australia Australia
Simon Segal resides in Melbourne Australia, is a certified MCAD, MCSD, MCDBA, MCSE, MCST BizTalk Specialist and has been working in the Software Development industry for some 10 years now. His key area of interest are distributed systems / SOA built with Microsoft technologies.

Comments and Discussions

 
GeneralFind me here.... PinmemberSimon Segal20-Jun-08 21:40 
GeneralXsd files PinmemberBlackSneak7-Mar-07 17:59 
GeneralRe: Xsd files PinmemberSimon Segal7-Mar-07 18:08 
GeneralProblems Running Pinmembersteve_fortner10-Nov-05 4:11 
GeneralRe: Problems Running Pinmembersteve_fortner10-Nov-05 6:14 
GeneralBulk Load Pinmemberperlmunger16-Nov-04 5:34 
GeneralRe: Bulk Load PinmemberSimon Segal16-Nov-04 12:21 
GeneralArticle Layout PinmemberA. Riazi8-Jun-03 10:12 
GeneralRe: Article Layout PinmemberSimon Segal8-Jun-03 15:44 
GeneralRe: Article Layout PinsitebuilderUwe Keim8-Jun-03 19:36 
GeneralRe: Article Layout PinmemberSimon Segal8-Jun-03 21:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 5 Oct 2003
Article Copyright 2003 by Simon Segal
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid