Managed
Classes - SQLXML
Microsoft
SQLXML 3.0 supports features that allow you to write applications to access XML
data from an instance of Microsoft SQL Server, bring the data into the
Microsoft .NET Framework environment, process the data, and send the updates
back to SQL Server. The SQLXML managed classes give you an arsenal of methods
you can use to capitalize on the Miscrosoft .NET Framework features. In this
article lets take a ride on these various methods and how we can use them in
paractical scenario. So lets get started ...
Note:
All examples in this article work on the pubs database. I also assume that
setting up of SQLXML virtual directoty is known to the reader.
XMLReader
In
the example below we would use the SQLXMLReader to return an XMLReader object.
'
Get the connection String
Dim
connStr
As
String
="DSN=localserver;uid=sa;pwd=password;initial catalog=pubs"
'
Creating an Command Object
Dim
cmd
As
New
SqlXmlCommand(connStr)
'
Other declarations
Dim
oXmlReader
As
XmlReader
Dim
ds
As
New
DataSet()
'
Root tag to be applied as this will have multiple parent
cmd.RootTag
= "Authors"
'
Defining commandtype
cmd.CommandType
= SqlXmlCommandType.Sql
'
Defining the query to get the data
cmd.CommandText
= "select * from authors for xml Auto"
'
Get the XML data into the DataReader
oXmlReader
= cmd.ExecuteXmlReader
ds.ReadXml(oXmlReader)
'
Display the XML
TextBox1.Text
= ds.GetXml
This
is a simple code that returns you a XMLReader. There are a couple of
interesting points to note here. the command object used is the SQLXMLCommand
object. This exposes a couple more properties specific to SQLXML features.
One such property is the Roottag. What this property does is to
form the root node as Authors as in this example. And make the XML returned
from the SQL Server a valid and well formed XML.
Client-Side
Formatting
This
example illustrates the use of the ClientSideXml property. The
application executes a stored procedure on the server. The result of the stored
procedure (a rowset) is processed on the client side to produce an XML
document. Note, that all it takes to make it work on the clientside is a
property with the command type.
'
Get the connection String
Dim
connStr
As
String
="DSN=localserver;uid=sa;pwd=password;initial catalog=pubs"
'
Creating an Command Object
Dim
cmd
As
New
SqlXmlCommand(connStr)
Dim
oXmlReader
As
XmlReader
Dim
ds
As
New
DataSet()
cmd.RootTag
= "Authors"
'
Note this extra clause
cmd.ClientSideXml
=
True
cmd.CommandText
= "exec GetAuthors FOR XML RAW"
oXmlReader
= cmd.ExecuteXmlReader()
ds.ReadXml(oXmlReader)
'
Display the XML
TextBox1.Text
= ds.GetXml
This
is a very useful property as the rowset can be from any datasource that may
support / not support XML based programming. Hence by using SQLXML we can from
hereon form the XML at the web-server.
XMLAdapter
The
next variation to the example is using the SQLXML adapter to get the xml from
SQL Server. What we have done in this example is to illustrate to you how we
can use Schemas and get data rather than the conventional Select statement.
'
Get the connection String
Dim
connStr As String="DSN=localserver;uid=sa;pwd=password;initial
catalog=pubs"
Dim
cmd As New SqlXmlCommand(connStr)
cmd.RootTag
= "Root"
'
This is the XPath we are specifying
cmd.CommandText
= "Authors"
cmd.CommandType
= SqlXmlCommandType.XPath
cmd.SchemaPath
= "../AuthorsSchema.xsd"
Dim
ds As New DataSet()
Dim
oAdapter As New
SqlXmlAdapter(cmd)
oAdapter.Fill(ds)
'
Display the XML
TextBox1.Text
= ds.GetXml
The
schema defines the database structure to retrieve the data. The command
text used acts as a XPath query to the resultset. In this example we are
querying out the authors root node. The schema used looks like :
<?xmlversion="1.0"encoding="utf-8"?>
<xs:schemaxmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:elementname="Authors">
<xs:complexType>
<xs:sequence>
<xs:elementname="au_id"type="xs:string"/>
<xs:elementname="au_lname"type="xs:string"/>
<xs:elementname="au_fname"type="xs:string"/>
<xs:elementname="phone"type="xs:string"/>
<xs:elementname="address"type="xs:string"minOccurs="0"/>
<xs:elementname="city"type="xs:string"minOccurs="0"/>
<xs:elementname="state"type="xs:string"minOccurs="0"/>
<xs:elementname="zip"type="xs:string"minOccurs="0"/>
<xs:elementname="contract"type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
If you were to use the Visual Studio .NET IDE then just a drag and drop from
the Server Explorer would geenrate the XSD. But the name space will not get
imported. You need to explicitly import the same. The interesting part in
the schema is the use of the sql namespace at line 3. Without which this
example wouldnot work.
DataSet
In
all the examples we have got the XML structure and have converted the same into
a DataSet. But here is yet another simple way by which we can get the XML from
SQL Server into a dataset. This is using the URL method. The code block for
this example:
Dim
ds As New DataSet()
ds.ReadXml("http://localhost/demo/templates/template.xml",
XmlReadMode.InferSchema)
'
Display the XML
TextBox1.Text
= ds.GetXml
Hence you can see that we have used the template method here to retrieve the
data. The URL when hit from the browser yields: 
And
the template file we have used is also a simple one which selects from the
Authors table. And the code for the template file is:
<?xml
version="1.0"
encoding="utf-8"
?>
<Authors
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<
sql:query
>
Select
* from pubs..Authors
For
XML Auto, Elements
</
sql:query
>
</Authors>
Again
in this also you need to note that we have used the xml namespace.
Conclusion
Here
are some of the elegant ways by which you can code and use SQLXML Managed
provider and get XML output from SQL Server. I hope you have enjoyed
this interesting ride with me.
Download the source code
XML
related Articles:
|