XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0
(Page 1 of 9 )
In the final part of his series, Jayaram Krishnaswamy discusses accessing XML data using Visual Studio .NET 2003 IDE. With the introduction of SQLXML3.0, the capabilities of XML data retrieval are vastly extended. The article discusses this topic using the Visual Studio.NET 2003 IDE and provides help on detailed coding. Specifically, it explores the SqlXmlCommand class in depth, delineating its properties and methods.
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.
XML data access using SqlCommand Class 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:
Next: XML data using Dataset >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
