Parametrizing SQLXML
There
have been a series of articles that have got published till now on how we can
use various techonolgies to retrieve data from SQLXML. In this article we would
get one step further in analyzing how we can parameterize each of these methods
to simulate realtime scenarios. In practical life we would like to filter out
select statements criterias. And we in quest to explore each.
Note:
Following code is written in VB .NET and familiarity with SQLXML managed
classes is expected
SQL
Statements
For
dynamic adhoc type queries we can simply use the SQLXMLParameter object to
specify the values. The following code snippet would illustrate this concept:
Dim
cmd As SqlXmlCommand
cmd = New SqlXmlCommand("DSN=localserver;initial
catalog=pubs")
cmd.RootTag = "Demo"
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "Select * from authors where au_id = ? FOR XML AUTO"
Dim oParam As
SqlXmlParameter
oParam = cmd.CreateParameter
oParam.Name = "au_id"
oParam.Value = "172-32-1176"
Dim oXR As
XmlReader
oXR = cmd.ExecuteXmlReader
Now that we have the data in a XML Reader object we can directly load the same
into an XMLDocument or DataSet and use the XML.
Stored
Procedures
Next
typical scenario is to parameterize the Stored procedure calls. This is very
much similar to the previous example int he implementation. Sample code snippet
would illustrate the procedure:
Dim cmd As
SqlXmlCommand
cmd = New SqlXmlCommand("DSN=localserver;initial
catalog=pubs")
cmd.RootTag = "Demo"
cmd.ClientSideXml = True
cmd.CommandText = "Exec GetAuthors ? FOR XML NESTED"
Dim oParam As
SqlXmlParameter
oParam = cmd.CreateParameter
oParam.Value = "172-32-1176"
Dim oXR As
XmlReader
oXR = cmd.ExecuteXmlReader
Seeing the code above we can see how similar the code blocks are. We have just
introduced two new concepts here. One is the use of Client Side XML formatting
and secondly we used SPs to get the data for us unlike in the previous example
where we used Adhoc sql query.
Template
Parameters
Next
in the example is the use of template based parameters. First we have to build
an template file to accept a parameter. Following code will help you generate
the same:
<?xml
version="1.0"
encoding="utf-8"
?>
<Demo
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param
name='au_id'>ISNULL</sql:param>
</sql:header>
<sql:query>
SELECT
authors.au_id, authors.au_lname, authors.au_fname
FROM
dbo.authors authors
WHERE
au_id = @au_id FOR XML AUTO
</sql:query>
</Demo>
Use the above XML file and create an template file. If you notice we have use
ISNULL for the sql:param element. This denotes that we would pass the parameter
value as ISNULL when the value is actually NULL. Hence like the previous
examples we change a couple of lines and we can execute an template like:
Dim cmd As
SqlXmlCommand
cmd = New SqlXmlCommand("DSN=localserver;initial
catalog=pubs")
cmd.RootTag = "Demo"
' The Template file path
cmd.CommandText = "../Authors.xml"
cmd.CommandType = SqlXmlCommandType.TemplateFile
Dim oParam As
SqlXmlParameter
oParam = cmd.CreateParameter
oParam.Name = "@au_id"
oParam.Value = "172-32-1176"
Dim oXR As
XmlReader
oXR = cmd.ExecuteXmlReader
XPath Parameters
This
is yet another parameterization we can achieve while using templates. I
wouldnot dwell into this topic much. But would surely give you a simple code
snippet to use the same for sure. Parameterizing templates look something like:
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[au_id=$au_id]"
cmd.CommandType = SqlXmlCommandType.XPath
cmd.SchemaPath = "../AuthorsSchema.xsd"
Dim oParam As
SqlXmlParameter
oParam = cmd.CreateParameter
oParam.Name = "@au_id"
oParam.Value
= "172-32-1176"
Dim oXR As
XmlReader
oXR = cmd.ExecuteXmlReader
And the schema used for the above example is :
<?xml
version="1.0"
encoding="utf-8"
?>
<xs:schema
xmlns:xs=http://www.w3.org/2001/XMLSchema
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element
name="Authors">
<xs:complexType>
<xs:sequence>
<xs:element
name="au_id"
type="xs:string"
/>
<xs:element
name="au_lname"
type="xs:string"
/>
<xs:element
name="au_fname"
type="xs:string"
/>
<xs:element
name="phone"
type="xs:string"
/>
<xs:element
name="address"
type="xs:string"
minOccurs="0"
/>
<xs:element
name="city"
type="xs:string"
minOccurs="0"
/>
<xs:element
name="state"
type="xs:string"
minOccurs="0"
/>
<xs:element
name="zip"
type="xs:string"
minOccurs="0"
/>
<xs:element
name="contract"
type="xs:boolean"
/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Conclusion
I
think we have come a long way in understanding how to parameterize each of
these methods. SQLXML has to be considered as not an add-on to the existing SQL
Server 2000 functionalities but as a part of it. We can be least assured that
the next version of SQL Server would have each of these features as inbuilt
functionalities. XML becoming the defacto method of communication between
layers these capabilties of SQLXML can maximize throughput with less
development costs.
|