Using SQLXML 3.0 & the Managed Classes
4.79/5 (29 votes)
How to use SQLXML 3.0 Managed Classes and the SQLXML modes.
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 |
The SQLXML FOR XML Clause and ModesThe basic syntax is as follows: FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]
The ArgumentsThe XMLDATAInstructs SQL Server to return an XML-Data schema which is included in the document inline. ELEMENTSEnsures 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 BASE64Represents binary data in base64-encoded format. |
For XML RAWRaw 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.0SELECT
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'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 SELECT
au_id as AuthorId,
au_fname as FirstName,
au_lname as LastName
FROM
authors
FOR
XML RAW, XMLDATA |
For XML AUTOThe Code Listing 2.0SELECT
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 |
|
|
The SQLXML Managed Classes [The main players]SqlXmlCommandThe Code Listing 4.0This code snippet outlines an example of using the VB.NETDim 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 BackIn the code above you will notice the Two more interesting methods of the Client Side and Legacy ApplicationsThe Code Listing 5.0VB.NETDim 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 SqlXmlAdapterThe Code Listing 6.0VB.NETDim 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 SQLXML TemplatesSQLXML 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 Code Listing 7.0VB.NETDim 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.
SqlXmlParameterThe 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.0VB.NETDim 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;
|
