XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0

Most examples will be from the pubs database, the example database that is installed with the SQL 2000 Server. The ADO.NET support for out of the box installation of SQL 2000 Server has the capability of retrieving XML data from the server as discussed in earlier articles. The data that is retrieved is further processed by the classes in the System.XML namespace.

In Part 1 of this series, I discussed the XML support in IIS for SQL 2000 Server in detail. Part 2, focussed on URL access of data from a SQL 2000 server using XML over HTTP. In Part 3 of this series, I developed the data access mechanism using the XPATH queries, again using the SQL 2000 Server specific virtual folder in IIS.

In an earlier article, SqlDataReader 1, retrieving XML data from TSQL extensions using ForXML(Raw|Auto|Explicit) was discussed that used the SqlCommand Class. The XMLReader being an abstract class does not have the capability of processing an XML document. While the XMLReader reads the data, forward-only, read-only, it needs the assistance of other classes that inherit from the XMLReader class. The XMLTextReader and XMLNodeReader are capable of processing what is read by the XMLReader. Similarly, the XMLTextWriter provides assistance for the XMLWriter abstract class.

As discussed in Part1 the TSQL extensions; ForXML clause does not retrieve an XML document, but just an XML fragment. The XMLTextReader uses its methods and properties to give a proper formatting necessary for an XML document.

Again, in an earlier article, SQLReader 2, step-by-step procedure for making the connection to a database, configuring the SQLCommand, using the query analyser- procedures neccessary for interacting with the databases was discussed in great detail. Major portions of these articles will be assumed to have been reviewed by the readers of this article. Only some major steps pertinent for the present discussion will be indicated.

We create a ASP.NET web application. To the web form we drag and drop a SQLConnection1 control and configure it to connect to the pubs database on the XPHTEK with windows authentication. This connection string summarizes this connectivity,

workstation id=XPHTEK; packet size=4096; integrated security=SSPI;data source=XPHTEK;persist security info=False; initial catalog=pubs

[If you ae running on windows you may just need to make changes to the workstation ID and the datasource- the name of the SQL 2000 Server on your machine]

Next a SQLCommand1 is dropped into the design area and the query designer is used to set up the following SQLCommand[see the next picture],

SELECT publishers.pub_id, publishers.pub_name, publishers.city, publishers.state, titles.title FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id FOR xml auto

It is implied that SqlCommand1 uses the SqlConnection1 made earlier. We have seen earlier, in Part1, that this query produces an XML fragment. In order to process this fragment further, we seek the assistance of the XMLTextReader mentioned earlier, which is the fastest processor for XML returned by the query. The returned XML fragment is persisted to a file, using a StreamWriter. The next code executes what has been discussed so far(remember to include imports for Systeem.IO and System.Xml). The XML data retrieved from the server is persisted(saved) to an XML file, XMLTextReader.xml. This file should have the write permission. 

Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
SqlConnection1.Open()'uses dropped control
Dim cmd As SqlClient.SqlCommand
cmd = SqlCommand1'uses dropped control
'Return the results to an XmlTextReader        
Dim xDoc As XmlTextReader
xDoc = cmd.ExecuteXmlReader()
'Process the XML Fragment        
xDoc.MoveToContent()
Dim fileWriter As New StreamWriter("C:\XmlTextReader.xml")
'the following line adds XML processing
fileWriter.WriteLine("<?xml version='1.0'?>")
'the following line supplies the missing ROOT tag
fileWriter.WriteLine("<Pubs_publishers>")
Do While xDoc.IsStartElement
fileWriter.WriteLine(xDoc.ReadOuterXml())
Loop
'thefollowing line adds the closing ROOT tag
fileWriter.WriteLine("</Pubs_publishers>")
'Close the XmlTextReader to free the connection        
xDoc.Close()
'Clean up         
SqlConnection1.Close()
fileWriter.Close()
Response.Write("File written")
End If
End Sub

The XMLTextReader.xml file opened in a text editor is as follows:

XML data using Dataset

The dataset set is a very important class as it represents the dual nature of data, its existence in a relational format(table) and its internal representation as XML. It provides a disconnected view of the data on the server. The following picture shows the partial view of Dataset class as seen in the ClassViewer.

So how does the data retrieved from server in XML? A SQLConnection is made to the server(as in the previous case), a SQLCommand is created to grab the XML fragment using the TSQL extension clause(as in the previous case), a SQLDataAdapter uses this command as its argument. The SQLDataAdapter's fill method, populates the DataSet's table. The Dataset calls its WriteXML method to the MemoryStream. The stream from this StreamReader can then be displayed.

An ASP.NET web application is started and a SqlConnection1 is added to the web form and then a SqlCommand1 control added and configured as mentioned in the previous case. Additionally, a Textbox control is added to the web form to receieve the stream data. In the Page_load event, the following code is written(remember to include imports for Systeem.IO and System. Xml).

Private Sub Page_Load(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
SqlConnection1.Open()'dropped,configured control
Dim cmd As New SqlClient.SqlCommand
cmd = SqlCommand1'dropped, configured control
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim ds As New DataSet("Titles")
da.Fill(ds, "Publishers")
SqlConnection1.Close()
'Write the DataSet as XML to a Stream        
Dim strm As New MemoryStream
ds.WriteXml(strm, XmlWriteMode.IgnoreSchema)
strm.Position = 0
Dim strmR As New StreamReader(strm)
''Display the contents of the stream 
TextBox1.Text = strmR.ReadToEnd
strm.Close()
End If
End Sub

The result of building this project and browsing the web form in IE yields the following,

Enter SQLXML 3.0

In early Jan 2002, with SQLXML3.0 release, a number of additional functionalities for XML support of SQL 2000 were introduced. Briefly, the following additional features were added, but in this tutorial we look mainly at data access using SQLXML3.0 with the Visual Studio .NET 2003 IDE.

• Web Services (SOAP) Support
• Client-side FOR XML
  Server processed queries were xml formatted on client side
• XML Schema Definition language (XSD)
  Create XML view of relational data
• Updategrams
  DML procedures with an UpdateGram
• Bulk Loading XML data
• Support for Microsoft .NET Framework
  SQLXML Managed Classes can be referenced from within
  Microsoft .NET Framework.
  The DiffGram format is supported.

XML data access using SqlXmlCommand Class

SQLXML 3.0 provide support for applications to access XML data from an instance of SQL 2000 server, port the data to the Microsoft.NET environment,process data, and send back the data to the SQL Server. These are effectively carried out obtaining support from the Microsoft Managed Classes. When SQLXML3.0 is installed, the Microsoft.Data.SqlXml libararies can be accessed from the application by adding a reference. In the codes that follow, the imports statement would make a reference to Microsoft.Data.SQLXml. The SQLXML managed classes object model has the following objects, of which the SqlXmlCommand class will be explored in detail.

• SqlXmlCommand
• SqlXmlParameter
• SqlXmlAdapter

Properties and methods supported by the SqlXmlCommand[Microsoft.Data.SqlXml.SqlXmlCommand] object are shown followed by the ADO.NET's SQLCommand [System.Data.SqlClient.SqlCommand]object in the next two pictures. A number of methods and properties[red rectangles in sqlxmlcommand object] add a great deal of support for XML based applications.The SqlXmlCommand class will be explored in detail with concrete examples.

Analogous to the SQLCommand's CommandType, there is a SQLXMLCommand's CommandType, notice the differences in the pictures shown. It is clear that, if SqlCommand is tailored for relational data, SqlXmlCommand is well suited for handling XML.

Exploring the SqlXmlCommand Class

In order to effectively exchange data both ways between realtional and xml, it is necessary to understand the workings of the SqlXmlCommand class, especially the properties and methods that make this data access possible. In the previous picture a number of properties and methods were identified [red rectangles], and these will be explored in this section. Here follows a list of properties and methods of SqlXmlCommand Class.

• TransactSQL Extensions:
  • SqlXmlCommand's ExecuteStream Method
  • SqlXmlCommand's RootTag Property
  • SqlXmlCommand's NameSpace Property
  • SqlXmlCommand's ExecuteToStream Method
  • SqlXmlCommand's ExecuteXMLReader
• Stored Procedures:
  • Using named Stored procedure in CommandText & ExecuteStream
• Templates/Template files:
  • Using CommandType=Template and a CommandText with
    Template String
  • Using CommandType=TemplateFile and referencing
    a TemplateFile for CommandText
• Styling with XSLT:
  • Using the BasePath and XSLPath properties.

The SQLXMLCommand uses the connection described by the connection string and issues a ExecuteStream method. The Command is configured to return a XML Fragment by its CommandText property. Stream result is captured in the StreamReader and the read stream is sent to the textbox. The stream sent to textbox is shown in the picture following the code.

Reference System.IO and Microsoft.DATA.SqlXml.

Private Sub Page_Load(ByVal sender As System.Object,
 ByVal e As System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand
("Integrated Security=SSPI;Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;
Auto Translate= True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for
  Data=False")
sxcmd.CommandText = "select pub_id, price, 
type,ytd_sales,titleauthor.title_id,au_ord from titles, 
titleauthor where titleauthor.title_id= titles.title_id andprice>15 
order by pub_id for xml auto"
'Memory stream is declared
Dim strmResults As MemoryStream
strmResults = sxcmd.ExecuteStream()
Response.Write(strmResults.Length)
Dim strmReader As New StreamReader(strmResults)
TextBox1.Text = strmReader.ReadToEnd
End If
End Sub

SqlXmlCommand's RootTag Property

As discussed in Part 1, Part 2, the TSQL extensions forXML returns but a fragment. It is possible to use the SqlXmlCommand's RootTag property to add the missing root for the returned result as shown in the following code. The Length property of the stream give the number of characters returned. The result of browsing this web page is shown in the picture that follows the code. Again references to System.IO and Microsfot.Data.SqlXml should be made.

  Private Sub Page_Load(ByVal sender As System.Object, 
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;Auto Translate= 
  True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 
  Data=False")
sxcmd.CommandText = "select pub_id, price, type,ytd_sales,
titleauthor.title_id,au_ord from titles, titleauthor
 where titleauthor.title_id= titles.title_id andprice>15 order by pub_id 
for xml auto"
Dim strmResults As MemoryStream
'RootTag is added before calling the ExecuteStream
sxcmd.RootTag = ("PubsTitles")
strmResults = sxcmd.ExecuteStream()
Response.Write(strmResults.Length)
Dim strmReader As New StreamReader(strmResults)
TextBox1.Text = strmReader.ReadToEnd
End If
End Sub

Just like adding the RootTag, it is also possible to add a Namespace using the NameSpaces property of the SqlXmlCommand object. Adding a NameSpace would help in referencing a schema for the XML results. The code for adding a namespace is shown together with the result following the code

Private Sub Page_Load(ByVal sender As System.Object,
 ByVal e As System.EventArgs) 
Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;Auto Translate= 

  True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 


  Data=False")
sxcmd.CommandText = "select pub_id, price, type,ytd_sales,
titleauthor.title_id,au_ord from titles, titleauthor 
where titleauthor.title_id= titles.title_id andprice>15 order by pub_id 
for xml auto"
Dim strmResults As MemoryStream
'adds a root tag
sxcmd.RootTag = ("PubsTitles")
'add a namespace
sxcmd.Namespaces = "xmlns=""www.mysorian.com"""
strmResults = sxcmd.ExecuteStream()
Response.Write(strmResults.Length)
Dim strmReader As New StreamReader(strmResults)
TextBox1.Text = strmReader.ReadToEnd
End If
End Sub

SqlXmlCommand's ExecuteToStream Method

The returned XML data can be added to an existing stream which would help in persisting the data. Assuming an existing FileStream, it is possible to use the ExecuteToStream method of the SqlXmlCommand to add the data to the file stream. Herein, a new FileStream is created defined by the path and filename, filemode and the ExecuteToStream method is called. The code is shown here, but remember to add the imports mentioned earlier. The result of this code is persisted to a file, shown by the link after the code.

Private Sub Page_Load(ByVal sender As System.Object, 
ByVal e As System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;Auto Translate= 
  True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 
  Data=False")
sxcmd.CommandText = "select pub_id, price, type,ytd_sales,
titleauthor.title_id,au_ord from titles, 
titleauthor where titleauthor.title_id= titles.title_id andprice>15 
order by pub_id for xml auto"
sxcmd.RootTag = "Publishers"
Dim strmResults As New FileStream
("C:\streamwritten.xml", FileMode.Append)
sxcmd.ExecuteToStream(strmResults)
strmResults.Close()
Response.Write("Retrieved")
End If
End Sub

View the result[streamwritten.xml] of executing the above code

SqlXmlCommand's ExecuteXMLReader

ExecuteXMLReader method returns the SQL 2000 Server data to the XMLTextReader, an implementation of the XMLReader class. This provides a fast forward, navigation process for the retrieved result. The code shows how the method is invoked and the result(truncated to show couple of lines) of executing this code. References to System.IO and Microsoft.Data. SqlXml are not shown here.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
  System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;
Initial Catalog=pubs;Use Procedure for Prepare=1;Auto Translate= True;
Persist SecurityInfo=False;Provider='SQLOLEDB.1';Workstation ID=XPHTEK;
Use Encryption for Data=False")
sxcmd.CommandText = "select au_fname, au_lname,city, 
phone from authors for xml auto"
sxcmd.RootTag = "Publishers"
'declare a XMLTextReader
Dim strRes As XmlTextReader
strRes = sxcmd.ExecuteXmlReader
'do some diagnostics
Response.Write("The read state is ; " & strRes.ReadState)
Response.Write("<br>Does it have attributes? " & 
            strRes.HasAttributes)
Response.Write("<br>How many attribues? " & strRes.AttributeCount)
While strRes.Read
strRes.WhitespaceHandling = WhitespaceHandling.None
Response.Write(strRes.Item("au_fname") & "<br>")
End While
strRes.Close()
End If
End Sub

Using Stored Procedure & ExecuteXmlReader

Consider the following stored procedure created in the 'localhost' [SQL 2000 Server XPHTEK that is being used in all the examples] which retruns the full name of a person which has First Name and Last Name stored in one of its tables. The name of the stored procedure is fullNameXML. Notice that this would return a XML fragment

Create proc fullNameXML
as
select (au_fname+' '+au_lname) as fullName, phone
from authors
for xml auto

The CommandText property is set to execute the stored procedure and an XMLReader reads the returned results after executing the ExecuteXmlReader. A root tag is also added to the fragment. The result of browsing this page is shown after the code listing.

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
System.EventArgs)
Handles MyBase.Load 'Put user code to initialize the
page here If Not
IsPostBack Then Dim sxcmd AsNewSqlXmlCommand
("PROVIDER=SQLOLEDB.1;SERVER=XPHTEK;DATABASE=pubs;INTEGRATED SECURITY=sspi;")
sxcmd.RootTag = "FullName"
sxcmd.CommandText = "exec fullNameXML"
Dim xrdr As System.Xml.XmlReader
xrdr = sxcmd.ExecuteXmlReader
While xrdr.Read
Response.Write(xrdr.Item("fullName") & "<br/>")
End While
xrdr.Close()
End If
End Sub

Using CommandType Template & ExecuteStream Method

In Part1 and Part2, templates have been discussed in detail. Please review the material on templates for a background to this topic. XML data can be retrived using templates. There are two options, in one of the options you supply the template in the form of a string to the CommandText property. In the other you give reference to a template file including its name and its path. In either case, you need to set the CommandType to the proper type as was seen in this picture. If you choose the template string then you should use the SqlXmlCommandType.template and in the other use SqlXmlCommandType. templatefile. The following code uses a string[the template] to feed the CommandText, the result follow the code.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
  System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
'create command object for template
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;Auto Translate=
  True;Persist Security Info=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 
  Data=False")
sxcmd.CommandText = "<root xmlns:
sql='urn:schemas-microsoft-com:xml-sql'><sql:query>select 
(au_fname+' '+au_lname) as fullName from authors for xml 
  auto</sql:query></root>"
Response.Write(sxcmd.CommandText)
sxcmd.CommandType = SqlXmlCommandType.Template
'Response.Write(Server.HtmlEncode(sxcmd.CommandText))
Dim sm As MemoryStream
sm = sxcmd.ExecuteStream()
Dim strmr As New StreamReader(sm)
TextBox1.Text = (strmr.ReadToEnd)
strmr.Close()
sm.Close()
End If
End Sub

Using CommandType=TemplateFile

As mentioned previously, the CommandType is SqlXmlCommandType.TemplateFile and the path of the file needs to be indicated. In the example, a template file called TplAuthors.xml is stored in the application directory and a Server.MapPath method is used to provide its location. The code is as shown and the result follows the code

This is the TplAuthors.xml in the application folder

File Name: TplAuthors.xml
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
select (au_fname+' '+au_lname) as fullName 
from authors for xml auto
</sql:query>
</root>

Now the code for data access using this template file

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
  System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;Initial Catalog=pubs;
Use Procedure for Prepare=1;Auto Translate= 
  True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 
  Data=False")
sxcmd.CommandType = SqlXmlCommandType.TemplateFile
sxcmd.CommandText = Server.MapPath("Tplauthors.xml")
Response.Write(sxcmd.CommandText)
Dim sm As MemoryStream
sm = sxcmd.ExecuteStream()
Dim strmr As New StreamReader(sm)
TextBox1.Text = (strmr.ReadToEnd)
strmr.Close()
sm.Close()
End If
End Sub

XML data although eminently suited for transport does not have the look of HTML. This is where XSL steps in to transform XML into HTML that is universally liked as a display format. However, XSL is for transformations of not only HTML but to other formats as well. SqlXmlCommand objects properties can help transofrm the XML into presentable HTML format as shown with the following code. You may need a little backgorund on XSL and the recommended site is http://www.w3c.org/, the Mecca for everything about XML.

This code does several things. It uses a stored template file and executes the command and through its XslPath property it references a stored XSL style sheet to be included into the returned results. The resulting document is stored as an HTML file in the chosen location. The XML file follows the code in the XSL file provided to format the HTML. A truncated FullName.htm is shown in the screen shot.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
  System.EventArgs)
 Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;
Initial Catalog=pubs;Use Procedure for Prepare=1;
Auto Translate= True;Persist SecurityInfo=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for 
  Data=False")
sxcmd.CommandType = SqlXmlCommandType.TemplateFile
sxcmd.RootTag = "author"
sxcmd.CommandText = Server.MapPath("Tplauthors.xml")
Response.Write(sxcmd.CommandText)
sxcmd.XslPath = Server.MapPath("authors.xsl")
Dim strmResults As New FileStream
("C:\FullName.htm", FileMode.Append)
sxcmd.ExecuteToStream(strmResults)
End If
End Sub

authors.xsl file
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 version="1.0"> <xsl:template match="/"><html><body><h2>Full Name of Authors</h2><table border="1.0" bgcolor="gold"><tr><td>Full Name</td></tr><xsl:for-each select="root/authors"><tr><td><xsl:value-of select='@fullName'></xsl:value-of></td></tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet>

Conclusions

This tutorial, 4th part in this series, is focussed on the SQLXML3.0 enhancements that make working with XML a lot easier through its support by means of several properties and methods. Only the data access part has been touched in this tutorial and of course it is also possible to make changes to the database by using some of the other items left out, notably the UpdatGram and DiffGram. These will be discussed in a forth coming tutorial.

The codes shown in this tutorial were tested on Visual Studio .NET 2003 on XP Professional platform with the backend server running in the same box as the IIS 5.0. If the server references are made correctly in the connection string, cutting and pasting the code should work. The imports should include the System.IO and Microsoft.Data.SqlXml. However, when files are written to hard disk, permissions should be present and this is also true for database access and while executing stored procedures.