Using ADO to access SQLXML capabilities
Well we have used seen a number of articles on this
site to show the XML capabilities of SQL Server 2000. Starting from
simple FORXML clause to advanced Managed classes have been discussed
earlier. But in this process we have forgotten the common
development platform used using ADO. And this article indeed targets those
people who think that we have not covered the same ... :) ...
The ADO library provides five main objects for use in
accessing data: the Connection, Command, Recordset, Record, and Stream
objects. I assume that the basics on these are known to you. Hence moving
forward I would show you how we can use each of these objects to retrieve a XML
template data from SQL Server 2000.
Using MSSQLXML Dialect
This is something important to understand before
executing the actual query. To make sure that our OLEDB provider understands
that the query we submit is an actual template we need to do this. To achieve
this we use the dialect of the command object. This dialect is
represented as GUIDs (Global Unique Identifiers) to a corresponding MSSQLXML
dialect.
-
{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}
- represents a Transact-SQL query and is the default
-
{5D531CB2-E6Ed-11D2-B252-00C04F681B71} -
represents an XML template query
-
{EC2A4293-E898-11D2-B1B7-00C04F680C56} - represents
an XPath query
So we typically set this with the command object as :
cmd.Dialect =
"{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
And as I had mentioned earlier the command text is
nothing but our template query that uses the SQL namespace.
cmd.CommandText = "<Invoice
xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query>" & _
"SELECT OrderID, OrderDate FROM Orders " & _
"WHERE OrderID = 10250 FOR XML AUTO"&
_
"</sql:query></Invoice>"
Creating Command object to execute
Now that the command object is set with the
commandtext and teh proper dialect to identify that this is the template method
we are all set to execute this query to get the result. I assume that the
connection is made to the NorthWind database and we have associated the
connection object to the commands active connection. Having done this we are
just a step away from executing. But we need a holder for the results. Hence we
use the stream object to get the output and we display the same.
Dim stmout AS ADODB.Stream
Set stmout = CreateObject("ADODB.Stream")
stmout.Open
cmd.Properties("Output Stream") = stmout
cmd.Execute, , adExecuteStream
MsgBox stmLout.ReadText, vbInformation, "XML"
Hence the program sends us the XML got from SQL Server
using templates. This is an easy way to code with SQLXML using ADO.
What I've shown in this example is an
simple example and this can be extended to get complex queries done using
template based approach. You can also use the XMLHTTP post method to get the
records from SQL Server.
Well to just illustrate the same I use a HTTP post
method below to get the same XML output.
' Post the template.
Set xmlHttp = New MSXML2.xmlHttp
xmlHttp.Open "POST", "http://www.extremeexperts.com/..", False
xmlHttp.setRequestHeader "Content-Type", "application/xml"
xmlHttp.send ""
' Retrieve the results.
Getdata = xmlHttp.responseText
Change the URL to something you want to get
from SQLXML template and your output is into the string getData ... :) ...
Simple Isn't it ...
|