|
Introduction
With the introduction of tMicrosoft's XML SQL Server Technology Preview for
SQL Server 7.0 and SQL Server 2000, a host of XML functionality is no
available. Over the past months I have been using the Tech Preview and more
recently 2000, mainly in retrieving dynamic XML Documents. This article
will describe the different ways of retrieving XML documents, highlight my
experiences, and list the differences between the Tech Preview and 2000.
And finally I'll discuss how you can call these features from ASP and
Visual Basic (VB).
Three flavors of XML Output
To allow the retrieval of data in the XML format from SQL Server, the FOR
XML command has been added to the T-SQL syntax. Using the FOR XML command
with a sequel query allows the results to be generated as XML. The command
allows three variants RAW, AUTO, and EXPLICIT. If we take the following SQL
Query (see Figure 1), which uses the Pubs sample database shipped with the
SQL Server, and apply it to each of the modes (or variants), we gain an
understanding of the various types of XML output that each mode is capable
of producing. (For more information on the Pubs database see http://msdn.microsoft
.com/library/psdk/sql/pubs.htm)
SELECT store.stor_id as Id, stor_name as Name, sale.ord_num as OrderNo,
sale.qty as Qty
FROM stores store inner join sales sale on store.stor_id = sale.stor_id
ORDER BY stor_name
FOR XML <MODE>
The query in Figure 1 generates a result table that contains all the sales
and the stores from which those sales were made, in ascending alphabetical
order. We add the FOR XML command after the query, along with the mode we
require, e.g., FOR XML RAW.
Ideally the XML-document structure I will generate is as follows:
<Stores>
<Store Id='' Name=''>
</Sale OrderNo='' Qty=''>
</Store>
<Stores>
Lets see how we proceed.
RAW Mode
Here is a snippet of the structure generated by the RAW mode.
The XML document produced contains an element <Row>, which is
fixed, for each record of the result set generated by Figure 1. This is not
very useful because we have no control over the element naming and document
structure. The RAW mode falls short of the XML document I am looking to
create, and really has limited use.
AUTO Mode
Below is a cross section of the output structure generated by the AUTO mode.
As you can see the <STORE> and <SALE> tags have a
parent-child relationship, giving us the hierarchical structure we require.
This node relationship is determined on the order in which the tables are
declared within the query, with each table declared in the sequence
becoming a child of the previously declared table.
Look back at Figure 1 and notice the aliases given within the query
affect the names generated within the XML document. Using this, we can
control the labeling of the XML elements and attributes, and achieve the
naming convention we require.
Bingo! So the AUTO mode allows us to create the XML document we want, but
the following disadvantages become apparent with further use:
- We can create hierarchical structures, but only in a linear fashion,
as a parent node can only have one child and vice versa.
- Using aliases to create element and attribute names can become tedious
and is sometimes counterproductive to the readability of the query.
- We cannot have both attributes and elements within the document. Its
either all elements, specified using the ELEMENTS keyword, or the default,
which is attributes.
These shortcomings are addressed within EXPLICIT mode.
EXPLICIT Mode and the Universal Table
The EXPLICIT mode requires a little more explanation as we introduce a
different method of expressing the query represented in Figure 1. This
alternative method allows us to fully manipulate the XML document generated.
First, I'll show you how the query in Figure 1 is coded using the EXPLICIT
mode, followed by how this allows us to move beyond the functionality
available in the AUTO mode.
Here is how Figure 1 would be represented using the EXPLICIT mode.
-The Store Data
SELECT 1 as Tag,
NULL as Parent,
s.stor_id as [store!1!Id],
s.stor_name as [store!1!Name],
NULL as[sale!2!OrderNo],
NULL as [sale!2!Qty]
FROM stores s
UNION ALL
-- The Sale Data
SELECT 2, 1,
s.stor_id,
s.stor_name,
sa.ord_num,
sa.qty
FROM stores s, sales sa
WHERE s.stor_id = sa.stor_id
ORDER BY [store!1!name]
FOR XML EXPLICIT
The query may look a little convoluted at first, but it's only splitting up
the different sets of data (in our case Store and Sale) into separate
select statements. The statements are then combined using the UNION ALL
operator.
The Universal Table: The reason we code the query as above is to generate a
table that not only stores the data represented within the XML document,
but also contains metadata describing how the XML document should be
structured. The table generated by the above query is referred to as the
Universal Table, which is the format required by the sqlxml.dll in order
to generate the XML document. The Universal Table is transparent to the
coder, but it is useful to view and gain an understanding of the table
since it aids in development time and debugging. Here is the Universal
Table generated:
| Tag | Parent | store!1!id | store!1!name | sale!2!orderno | sale!2!qty |
| 1 | NULL | 7066 | Barnum's | NULL | NU
LL |
| 2 | 1 | 7066 | Barnum's | A297650 | 50 |
| 2 | 1 | 7066 | Barnum's | QA7442 | 375
|
| 1 | NULL | 8042 | Bookbeat | NULL | NU
LL |
| 2 | 1 | 8042 | Bookbeat | 423LL9 | 2215 |
The metadata sections of the Universal Table and EXPLICIT mode query are
highlighted in red, and the black text represents the data. If you compare
the query with the table, you will see which elements are required for the
sqlxml.dll to generate the XML document. Let's take a closer look at what
they actually describe.The Tag and Parent columns store the information for
how the XML document should be structured in regards to hierarchy. It is
useful to think of each select statement within Figure 2 as representing an
XML node, and the Tag and Parent columns allow you to specify where that
node resides within the hierarchy of the document. By specifying a Tag of 2
and a Parent of 1 within the second select statement, we are saying that
this data will be tagged with the value of 2 and the parent of this data is
the data tagged with the value of 1, i.e., the first select statement. This
allows us to create our parent-child relationship <Store> to
<Sale>, and as you have probably guessed, allows us to generate
any valid XML document structure. Note the first select statement has the
parent column set to NULL; this means the <Store> element will
reside at the lowest level.
The data columns, represented in black, which will become attributes or
elements of a given node, e.g., the store_ID, have information about them
passed via the column name. The column name, using a ! delimiter, describes
the data via four arguments, with the fourth as an optional parameter.
These arguments describe the following:
- The first item represents the element name the column belongs to, in
this case the <Store> element.
- The second is the tag number that specifies where the information
resides within the XML tree.
- The third argument specifies the attribute or element name within the
XML document. Here the name is specified as id.
- By default, a data column is created as an attribute of the node
specified in argument 2, so id would become an attribute of the
<Store> node. To specify id as an element of <Store>,
we can pass the optional fourth argument, which among other things, allows
us to specify the item as an element, e.g., store!1!id!element.
Due to using the UNION ALL operator to join our select statements, it is
imperative that we have the same amount of resulting columns within each
select statement to maintain the validity of the SQL query. Using the NULL
keyword we can pad out the select statements, which saves us from repeating
data.
The XML document generated by the EXPLICIT mode query is exactly the same
as the one created using the AUTO mode, so why create an EXPLICIT mode
query?
Imagine someone has requested that the discount information for a given
store must be present within our XML document. If we take a look at the
Pubs database, it becomes apparent that each store can have a range of 0 to
n discounts. So the logical step is to have the Discount element as a child
of <Store>, which gives you an XML structure as follows:
<STORES>
<STORE Id='' Name=''>
<DISCOUNT Type='' LowQty='' HighQty=''>
<AMOUNT></AMOUNT>
</DISCOUNT>
<SALE OrdNo='' Qty=''>
</SALE>
</STORE>
<.STORES>
The changes clearly defined are as follows:
- Amount will not be an attribute of the <Discount> element
but will be an element, nested within discount.
- There will be an additional XML element called <Discount>
placed at the same hierarchical level as the <Sale> element,
i.e., a child of the <STORE> element.
If you try to apply these changes using the AUTO mode, it soon becomes
apparent that it is not possible.
Below is the EXPLICIT mode query to create the new XML document.
SELECT 1 as Tag, NULL as Parent,
s.stor_id as [Store!1!id],
s.stor_name as [Store!1!name],
NULL as [Sale!2!orderno],
NULL as [Sale!2!1ty],
NULL as [Discount!3!type],
NULL as [Discount!3!lowqty],
NULL as [Discount!3!highqty],
NULL as [Discount!3!amount!element]
FROM stores s
UNION ALL
SELECT 2, 1,
s.stor_id,
s.stor_name,
sa.ord_num,
sa.qty,
NULL,
NULL,
NULL,
NULL
FROM stores s, sales sa
WHERE s.stor_id = sa.stor_id
UNION ALL
SELECT 3, 1,
NULL,
s.stor_name,
NULL,
NULL,
d.discounttype,
d.lowqty,
d.highqty,
d.discount
FROM stores s, discounts d
WHERE s.stor_id = d.stor_id
ORDER BY [store!1!name]
For XML EXPLICIT
Look at the changes made to the EXPLICIT mode query in Figure 2 in order to
create the new explicit query in Figure 2A:
- A third select query was added to extract the Discount data, and this
data was tagged with a value of 3, using the first column Tag.
- Discount data was made a child of the <Store> element by
specifying the Parent column as 1.
- Notice that I have also included only the column data I require, and
padded out the third select query using the NULL keyword. I have to include
the stor_name data within this query, although it is not used within the
Discount element. If the stor_name column was set to NULL, the resulting
Universal tTable would not be ordered in an ascending node sequence, which
is a requirement of the parser ( try it and see). Alternatively you could
order the Universal tTable using the Tag column.
- To maintain the integrity of the resulting Universal tTable, the first
and second select statements were padded using the NULL keyword to reflect
the additional columns added to house the discount data.
- The first select statement was amended to specify the metadata for the
new discount columns.
- The Amount column was specified as an element of Discount, using the
fourth argument, to declare the element directive. (The element directive
is one of several directives that can be passed/declared via the fourth
argument.)
Here is the document that is exclusive to the explicit mode.
The XML document produced does not display NULL data, e.g., discount lowqty
and highqty.
Templates
Templates are an excellent feature that allow us to wrap the RAW, AUTO, and
EXPLICIT mode queries into a valid XML document. By doing this we gain the
following advantages:
- We can
store our queries as persisted XML files, instead of having
to generate the queries at runtime using strings.
- We can pass parameters to the queries, such as the store id, so that
we can generate dynamic XML documents.
- We can specify an (Extensible Style Language) XSL document for which
our resulting XML document can be transformed with. XSL is a specification
for separating style from content when creating XML pages. Much like a
template, it allows designers to apply single-style documents to multiple
pages.
Note: The syntax for specifying a parameter-based query within the SQL 7.0
Tech Preview is slightly different from that of SQL Server 2000, the latter
been more intuitive.
Figure 3 shows how to wrap up an an AUTO mode query within a template.
<?xml version="1.0" ?>
<Stores xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:header>
<sql:param name='StoreId'>8042</sql:param>
</sql:header>
<sql:query>
SELECT store.stor_id, stor_name, sale.ord_num, sale.qty
FROM stores store inner join sales sale on store.stor_id = sale.stor_id
WHERE store.stor_id = @StoreId
FOR XML AUTO
</sql:query>
</root>
As you can see, the template is an XML document, making it easy to read and
hopefully to understand. Below is a breakdown of what each part of the
template does.
The <Stores> tag specifies the root element, which is a
requirement of keeping the XML well-formed. .Otherwise we could end up
with multiple <Store> tags at the root level, which would be
rejected by the parser. Within the root tag, we have the namespace
declaration urn:schemas-microsoft-com:xml-sql, which is required because it
declares the query and parameter tags. Note: The alias does not have to be
sql.
The <header> tag contains information pertaining to the
template, such as parameters to be passed in. At present the only tag you
can specify within the header is the <param> tag. The
<param> tag allows us to pass and create default parameters to
use within the body of the query, much like when you declare a variable
within a stored procedure. In Figure 3, a parameter named StoreId is
created with a default value of 8042, this value would be replaced if the
parameter was passed into the query.
The XML Tech Preview currently does not support the <header> and
<param> tags, so an alternative method of specifying the
parameters is used. Below is how the template would look using the XML Tech
Preview.
<?xml version="1.0" ?>
<Stores xmlns:sql="urn:schemas-microsoft-com:xml-sql" StoreId='8042'>
<sql:query>
SELECT store.stor_id, stor_name, sale.ord_num, sale.qty
FROM stores store inner join sales sale on store.stor_id = sale.stor_id
WHERE store.stor_id = ?
FOR XML AUTO
</sql:query>
</root>
As opposed to 2000, the parameter is declared within the root tag, again
declaring the parameter name as StoreId with a default value of 8042. The
parameter is referenced using the ? character, which can only be referenced
once for each parameter. This creates a problem if you are using an
EXPLICIT mode query that requires the variable to be referenced in multiple
places. The way to get around this is to capture the value in a variable
such as the one below, which then allows you to use it as many times as you
wish.
Declare @StoreId as integer
Set @StoreId = ?
Also note that within the Tech Preview and SQL Server 2000, you can specify
an XSL file for which the resulting XML document should be transformed
with. This requires little work and is extremely useful because it saves
you from applying the template after the template has executed.
<Stores xmlns:sql="urn:schemas-microsoft-com:xml-sql"
sql:xsl='Store.xsl'>
Executing Templates via Visual Basic
Now I need to actually execute my templates and retrieve the XML document.
With the Tech. Preview and SQL Server 2000 it is possible to set up a URL
using the Configuration mmc snapin to allow the execution of templates.
This uses an ISAPI DLL, that executes the queries and returns the result.
This tool is moderately configurable and also allows the user to add
security restrictions. Note that the Configuration tool ships with SQL
Server 2000 and also comes with the Tech Preview (see
http://msdn.microsoft.com/workshop/xml/articles/xmlsql/sqlxml_prev.asp).
The tool is very useful, but what if I want to include the execution of
these queries within my existing library of database (DB) functions, which
may have proprietary security methods and error trapping.
If you are using the Tech Preview, then you can use the bundled SQLXML Type
Library 1.0 to access the ExecuteTemplateFile function. If you are using
SQL Server 2000 which ships with ADO 2.6, then you will be able to use ADO
to execute the templates. Below are two wrapper functions that allow you to
execute templates using Visual Basic. One coded for the Tech Preview allows
parameters to be passed in, and another is coded for SQL Server 2000's
bundled ADO 2.6.
Points to note when using the functions
- The output coding of the resulting XML document is UTF-8, which is a
little more lightweight than using UTF-16 and also has the development
advantage that the XML document will be viewable within Internet Explorer.
- If you are looking to manipulate the document within Visual Basic
after it has been created, retrieve the document using Output Encoding
UTF-16.
Tech Preview Code
Points to note when using the XML Technology Preview code:
- To get this up and running you will need to reference the Microsoft
SQLXML Type Library 1.0 supplied with the XML Tech Preview.
- The resulting document is passed out using a BinaryWrite so we can
retrieve the document in the UTF-8 format.
- The parameters have to be passed as pairs and in the format (parameter
name, parameter value). The call to ExecuteTemplateFile will error if there
are an odd number of elementswithin the array
- The template file path has to be a full physical path, e.g.,
C:\MyTemplates\Template.xml.
- The GetConnectionString() is a function that I presume most people
have within their DB Library, which will create a connection string to the
database.
Function RunTemplateFileReturnXML(ByVal strTemplateFile As String, Optional
ByVal aParams As Variant) As Variant
On Error GoTo errorHandler
Dim oSQLXML As New MicrosoftSQLXML.SQLXMLRequest
' Init the ADO objects & the stored proc parameters
oSQLXML.Connection = GetConnectionString()
oSQLXML.OutputEncoding = "UTF-8"
' Check for params if so include them
If Not IsMissing(aParams) Then
oSQLXML.Parameters = aParams
End If
' Execute the Template File
oSQLXML.ExecuteTemplateFile (strTemplateFile)
RunTemplateFileReturnXML = oSQLXML.ResultAsBinary
Set oSQLXML = Nothing
Exit Function
errorHandler:
Set oSQLXML = Nothing
End Function
Below is the ASP code to access the RunTemplateFileReturnXML function.
' Create param list
aParams = Array("StoreId", "8042")
'Create the XML Helper Function Library
Set oXMLDb = Server.CreateObject("MyDBLibrary.clsMyDBLibrary")
'Create the physical path
sTemplate = "C:\MyTemplates\StoreTemplate.xml"
' Execute the Template file and return the results using BinaryWrite
Response.BinaryWrite oXMLDb.ExecuteTemplateFileXML(sTemplate, aParams)
This code could be used within an existing ASP page and manipulated server
side, or could be held within its own file, e.g., StoreXML.asp, which could
then be called directly from a XML data island on the client.
ADO 2.6 Code
Since ADO 2.6 is relatively new and documentation is a little thin, the
following code allows you to execute a template, but does not allow the
passing of parameters (which is similar to the Tech Preview code). It uses
streams to read and write the template file and XML result. The code works
in the same way as executing a stored procedure or SQL query, with the
addition of the cmd.Dialect property that specifies the Command object is
expecting a FOR XML or template query.
Function RunTemplateFileReturnXML(ByVal strTemplate As String) As Variant
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
' Open a connection to the SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=Pubs; uid=sa; "
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the input stream and write our template to it.
strmIn.Open
strmIn.Charset = "UTF-8"
strmIn.LoadFromFile (strTemplate)
strmIn.Position = 0
'Set the command object to input stream
Set cmd.CommandStream = strmIn
strmOut.Open
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
strmOut.Charset = "UTF-8"
RunTemplateFileReturnXML = strmOut.ReadText
End Function
Conclusion
Using the FOR XML command makes retrieving XML documents from your SQL DB
flexible and relatively easy to code. Add to this the ability to wrap up
those queries within a template and execute them via ASP, and you have a
flexible means of creating dynamic XML documents.
If you are using SQL Server 2000, then you have no worries about support
and performance. As for those using the XML Tech Preview version, it is
unsupported and may have performance issues within a production
environment, but it still gives you the opportunity to start using this
technology in a noncritical environment
About the Author
Steve Wood is a freelance software consultant in Bradford, England.
He has been developing commercial applications in Visual C++ for six years
and over the past two years has been designing and implementing Internet
applications using the Microsoft Windows DNA Architecture. He has worked on
a number of Internet and intranet projects, from B2C
projects such as www.learningshop.co.uk to B2B projects within the
healthcare sector (see www.medideskonline.co.uk).
Steve has a wide range of skills and focuses on site architecture, ASP, XML,
SQL, and middle-tier component development in VC++ and Visual Basic. He can
be contacted at info@stevenwood.org
|
