Home    Articles    XML in SQL Server 2000 and SQLXML

XML in SQL Server 2000 and SQLXML Monday, January 26, 2004

Summary
Microsoft® SQL Server™ 2000 (codenamed Shiloh) first introduced native support for XML and HTTP. Since then, to keep up with XML, Web services and .NET advancements, Microsoft has made available add-ons to SQL Server 2000 in the form of SQLXML Web releases. This article begins with explaining the built-in XML and HTTP support in SQL Server 2000 and then it describes the features available in the recent SQLXML Web release (version 3.0, service pack 2).

Let's begin by looking at what's built-into SQL Server 2000, features that you can use without installing any SQLXML Web release.

• extract relational data as XML,
• access relational data over HTTP,
• load and read XML as a rowset, and
• create XML views using annotated XDR schemas.

SQL Server 2000 supports XML 1.0, XML Namespaces, Microsoft's XDR (XML-Data Reduced) Schemas, XSLT 1.0, and contains partial support for XPath 1.0. It does not support XSD (SQLXML adds support for XSD schemas and is discussed later in this article).

SQL Server 2000 added an extension to SELECT T-SQL statement by introducing the FOR XML clause. The FOR XML clause specifies that the query results must be returned as an XML fragment, instead of the standard rowsets.

FOR XML {RAW | AUTO | EXPLICIT} [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

As per the above syntax, the FOR XML clause can be executed in one of the three modes: RAW, AUTO, or EXPLICIT:

• RAW:

  In RAW mode, each record in the resultant rowset is converted to an element generically named row, which contains an attribute for each of the columns that is retrieved.

  • No attribute is added to the row if the column value is NULL.
  • Each column must have a name. If any of the columns is unnamed, it produces the error 'Server: Msg 6809 Unnamed column or table names cannot be used as XML identifiers. Name unnamed columns using AS in the SELECT statement.'. For instance,

    SELECT SUM([Freight]) FROM [Orders] FOR XML RAW

    statement produces the 6809 error as the aggregated column does not have any name. To fix this, use the AS clause and give some name to the column.
  • ELEMENTS option is not available with the RAW mode.
  • If one of the returned columns is of binary type, BINARY BASE64 option must be used with the RAW mode, as the RAW mode does not support addressing binary columns as URLs (discussed later).
  • You can provide alias names for the columns in the SELECT query; these alias names will be used as the attribute names.
  • If your SELECT query contains JOINs, the RAW mode does not formulate a hierarchy in the resultant XML (as the AUTO mode does), and hence RAW mode leads to lots of data repetition in this case.
  Without any further due, let's look at an example to better understand the RAW mode:

  SELECT [E].[EmployeeID], [E].[LastName], [ET].[TerritoryID] 
  FROM [Employees] AS [E] JOIN [EmployeeTerritories] AS [ET] ON 
  [E].[EmployeeID] = [ET].[EmployeeID]
  FOR XML RAW

  The above SELECT query joins Employees and EmployeeTerritories tables in the Northwind sample database. The FOR XML clause causes database engine to emit results as XML stream instead of traditional rowset. Run this query analyzer and you should see the following text in the output window: (results abridged and formatted for clarity)

  XML_F52E2B61-18A1-11d1-B105-00805F49916B
  ----------------------------------------------------------------
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="02903"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="07960"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="08837"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="10019"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="10038"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="11747"/>
  <row EmployeeID="5" LastName="Buchanan" TerritoryID="14450"/>
  <row EmployeeID="8" LastName="Callahan" TerritoryID="19428"/>

  By default query analyzer limits the maximum characters per column to 255. You may want to increase it to maximum 8192 using the Results tab on the Tools | Options dialog box. Note that this option only affects the SQL Query Analyzer output window. Also "Results in Text" mode instead of "Results in Grid" is preferred when XML data is returned in Query Analyzer.

  Note that the result of above SELECT...FOR XML statement is a XML fragment, and not a well-formed XML document, as it does not have a single top-level element.

  Two other important things to note in the above output: each record is converted to an element named row; and that the JOIN did not result in hierarchical XML (one employee element with multiple child TerritoryID elements), but simple record to XML translation including repeating data.

• AUTO:

  AUTO mode differs the RAW mode in following ways:

  • Unlike generic element name "row" for each record, with AUTO mode, table names (or their aliases) are used to form the element names.
  • The order in which columns appear in a SELECT statement (with JOIN) determine the hierarchy of the returned XML.
  • You can use ELEMENTS option to tell SQL Server to produce child-elements (instead of attributes) for each resultant column.
  • If one of the returned columns is of binary type, it is not required to pass BINARY BASE64 option. In such cases, binary columns are addressed as URLs (discussed later) instead of inline base64 encoded value (when BINARY BASE64 is passed). However, if BINARY BASE64 option is not passed, it is required to have primary column in the SELECT column list.

  Here is a sample SELECT...FOR XML AUTO query:

  SELECT [E].[EmployeeID], [E].[LastName], [ET].[TerritoryID] 
  FROM [Employees] AS [E] JOIN [EmployeeTerritories] AS [ET] ON 
  [E].[EmployeeID] = [ET].[EmployeeID]
  FOR XML AUTO

  The above SELECT...FOR XML AUTO query results in following output (results abridged and formatted for clarity):

  XML_F52E2B61-18A1-11d1-B105-00805F49916B
  ----------------------------------------------
  <E EmployeeID="5" LastName="Buchanan">
     <ET TerritoryID="02903"/>
     <ET TerritoryID="07960"/>
     <ET TerritoryID="08837"/>
     <ET TerritoryID="10019"/>
     <ET TerritoryID="10038"/>
     <ET TerritoryID="11747"/>
     <ET TerritoryID="14450"/>
  </E>
  <E EmployeeID="8" LastName="Callahan">
     <ET TerritoryID="19428"/>
     <ET TerritoryID="44122"/>
     <ET TerritoryID="45839"/>
     <ET TerritoryID="53404"/>
  </E>

  Things to note/try:

  • Table alias names are used for naming elements. If table or alias names contains space or special characters, they are escaped to make the valid XML element names. For instance, a space character in the table name gets translated into _x0020_. Try FOR XML AUTO query on [Order Details] table in the Northwind sample database.
  • The hierarchy is based on the columns as they appear in the SELECT statement (try putting [ET].[TerritoryID] as the first column in the above query and see the resultant XML).
  • Put ,ELEMENTS (note the comma) at the end and note that columns are no longer attributes in the resultant XML, but are the sub-elements.
  • Select a image type column ([E].[Photo]) and try with and without ,BINARY BASE64 (note the comma) option at the end.

  Note that the FOR XML AUTO mode does not support queries that contain GROUP BY or aggregate functions. The nested query approach can be used to workaround this limitation.

  The following query produces error 6821 GROUP BY and aggregate functions are currently not supported with FOR XML AUTO"

  SELECT [ProductID], 
   SUM([UnitPrice]*[Quantity] - ([UnitPrice]*[Quantity]*[Discount])) 
       AS [Amount]
  FROM [Order Details] 
  GROUP BY [ProductID] 
  ORDER BY [Amount] DESC
  FOR XML AUTO

  To workaround this, try the following query:

  SELECT [ProductID], [Amount] FROM
  (
    SELECT [ProductID], 
     SUM([UnitPrice]*[Quantity] - ([UnitPrice]*[Quantity]*[Discount])) 
       AS [Amount]
    FROM [Order Details] 
    GROUP BY [ProductID] 
  ) ProductAmount
  ORDER BY [Amount] DESC
  FOR XML AUTO

  The above FOR XML AUTO nested query produces following XML fragment (results abridged and formatted for clarity):

  XML_F52E2B61-18A1-11d1-B105-00805F49916B
  -----------------------------------------------------------------
  <ProductAmount ProductID="38" Amount="1.413967352294922e+005"/>
  <ProductAmount ProductID="29" Amount="8.036867211914063e+004"/>
  <ProductAmount ProductID="59" Amount="7.115569999694824e+004"/>

• EXPLICIT:

  This mode provides the maximum control over the structure of resultant XML. You can specifically tell the database engine which column should be parent, child, attribute, included in CDATA section, escaped/un-escaped, and so on. However, this comes at a price of writing lengthy (and little complex) queries.

  EXPLICIT mode requires the first two columns in the SELECT statement to be named TAG and PARENT. These two meta-columns help the engine decide the hierarchy of the elements. The value of TAG column is a positive number indicating the level in the hierarchy; while the value of PARENT column (also an integer value) is the TAG number of column that this element will appear under within the hierarchy. For the topmost element the PARENT column value can be NULL or 0.

  Having the above two meta-columns as the first entries in the SELECT list, you can then list actual data columns.

  EXPLICIT mode requires each data column to be aliased using the following syntax notation:

  [ElementName]!TagNumber[!AttributeName][!Directive]

  At this point, I think an example will help. So, let's look at an FOR XML EXPLICIT example:

  SELECT 
        1 AS [Tag], 0 AS [Parent],
        [CustomerID] AS [Customers!1!CustomerID],
        [Address] + ' ' + [City] AS [Customers!1!!CDATA],
        NULL AS [Orders!2!OrderID],
        NULL AS [Orders!2!OrderDate!element]
        FROM [Customers]
  
  UNION ALL
  
  SELECT 
        2 AS [Tag], 1 AS [Parent],
        [Customers].[CustomerID],
        NULL,
        [OrderID], 
        [OrderDate] 
        FROM [Customers] JOIN [Orders] ON 
             [Customers].[CustomerID] = [Orders].[CustomerID]
        ORDER BY [Customers!1!CustomerID], [Orders!2!OrderID]
  
  FOR XML EXPLICIT

  For the above example, we wanted a two-level hierarchy: Customers details as parent node and then Orders details as the child elements. Hence, two SELECT statements are combined using the UNION ALL clause.

  Things to note/try:

  • The FOR XML clause is used just once – after the last query in the UNION.
  • In the first SELECT statement [Tag] is given value 1, which is then used as the value of [Parent] in the second SELECT statement, creating the Customers-Orders hierarchy. The [Parent] in first SELECT statement is 0 (can be NULL as well) indicating "no node above this element".
  • CDATA directive is used for the [Address] + ‘ ‘ + [City] computed column (more on directives soon). If CDATA directive is specified, you must not provide the attribute name (note the two exclamation marks followed by CDATA in the column alias).
  • element directive is used for [OrderDate] column.
  • It is very important to have the ORDER BY clause. Try the above query without ORDER BY and notice the difference.

  The above query results in following output (results abridged and formatted for clarity):

  XML_F52E2B61-18A1-11d1-B105-00805F49916B
  -------------------------------------------------------------------
  <Customers CustomerID="ALFKI">
       <![CDATA[Obere Str. 57 Berlin]]>
       <Orders OrderID="10643">
         <OrderDate>1997-08-25T00:00:00</OrderDate>
       </Orders>
       <Orders OrderID="10692">
         <OrderDate>1997-10-03T00:00:00</OrderDate>
       </Orders>
       <Orders OrderID="10702">
         <OrderDate>1997-10-13T00:00:00</OrderDate>
       </Orders>
       <Orders OrderID="10835">
         <OrderDate>1998-01-15T00:00:00</OrderDate>
       </Orders>
       <Orders OrderID="10952">
         <OrderDate>1998-03-16T00:00:00</OrderDate>
       </Orders>
       <Orders OrderID="11011">
         <OrderDate>1998-04-09T00:00:00</OrderDate>
       </Orders>
  </Customers>
  

  The above output is just one example of how you can customize the structure and hierarchy of XML using the EXPLICIT mode. The above example made use of CDATA and element directive. Following table lists other directives. Refer to SQL Server Books Online documentation for more details on this and for more examples. Once you fully understand the syntax, I would recommend trying out a lot of examples using FOR XML EXPLICIT to see its power.

  FOR XML EXPLICIT – Directives

  Directive		Description
  CDATA		Column data is wrapped in a CDATA section.
  element		Column data is presented as an element (instead of attribute, which is default behavior)
  hide		Columns with this directive are excluded from the resultant XML fragment.
  xml		This directive is similar to element directive – however unlike element directive, the xml directive does not escape the data.
  xmltext		If the column contains XML text/fragment, this directive can be used to integrate that column data into the resultant XML.
  ID, IDREF, and IDREFS		These directives are primary used to provide XML document knowledge of a database's primary and foreign keys. These directives are used in conjunction with XMLDATA option (discussed next).

  By now, I hope you have good understanding of all three modes in which FOR XML can be used. We also saw the ELEMENTS and BINARY BASE64 option. There is one more option that you can provide with FOR XML query: XMLDATA.

  XMLDATA causes XDR schema to be returned as part of the XML. Before we move to the next section, let's look at one final example:

  SELECT 
     [LastName] + ' ' + [FirstName] AS [Name],
     [Photo]
  FROM [Employees] 
  FOR XML AUTO, XMLDATA, BINARY BASE64, ELEMENTS

  The above query produces following output (results abridged and formatted for clarity):

  XML_F52E2B61-18A1-11d1-B105-00805F49916B
  --------------------------------------------
  <Schema 
     name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" 
     xmlns:dt="urn:schemas-microsoft-com:datatypes">
  
    <ElementType name="Employees" content="eltOnly" 
                 model="closed" order="many">
      <element type="Name"/>
      <element type="Photo"/>
    </ElementType>
  
    <ElementType name="Name" content="textOnly" 
                 model="closed" dt:type="string"/>
    <ElementType name="Photo" content="textOnly" 
                 model="closed" dt:type="bin.base64"/>
  </Schema>
  
  <Employees xmlns="x-schema:#Schema2">
    <Name>Davolio Nancy</Name>
    <Photo>FRwvAAIAAAANAA4AFAAhAP////</Photo>
  </Employees>

Things to note:

• XMLDATA option causes XDR schema to be pre-pended in the output.
• BINARY BASE64 causes image type column to be returned using base64 encoding.
• ELEMENTS option causes columns to be returned as sub-elements instead of attributes.

Let's look at an example of calling FOR XML from an ADO client application:

Let's first write a stored procedure that runs FOR XML query and returns the XML stream to the client:

CREATE PROCEDURE [dbo].[sproc_GetEmployeesXML]
AS
BEGIN
	SELECT *
	FROM [Employees] AS [EmpDetails]
	FOR XML AUTO, ELEMENTS, BINARY BASE64
END

Here is sample ASP page that uses ADO to call the above stored procedure. It receives the XML stream from the stored procedure and forwards that to the ASP Response stream. Before and after this code we manually generate the top-level element, making the XML document well-formed.

<%@ Language=VBScript %>
<%
   Response.ContentType = "text/xml"
   
   Dim objConn
   Dim objComm
   
   Set objConn   = Server.CreateObject("ADODB.Connection")
   Set objComm   = Server.CreateObject("ADODB.Command")
   
   objConn.Open "PROVIDER=SQLOLEDB;SERVER=.;UID=sa;PWD=sa;DATABASE=Northwind;"
   Set objComm.ActiveConnection = objConn
   
   Response.Write "<NWEmployees>"

   objComm.CommandText = "EXEC [dbo].[sproc_GetEmployeesXML]"
   objComm.Properties("Output Stream") = Response
   objComm.Execute , , 1024 'adExecuteStream = 1024

   Response.Write "</NWEmployees>"
   
   Set objComm = Nothing
   objConn.Close
   Set objConn = Nothing
%>

Previous section described how FOR XML can be used to turn relational rowset into XML. The OPENXML function does exactly opposite of that and can be used to turn XML into a relational rowset. This can be used for example if you have XML and would like to get a rowset view on top of it, which you can use to modify the relational table data.

Using OPENXML is a three step process:

Use sp_xml_preparedocument to load the XML document and create an in-memory representation of it. This stored procedure returns the handle (which can be used only in that particular connection). If your XML document contains namespaces (default or name-prefixed), you need to specify those with this stored procedure.
2. Call OPENXML, pass it the document handle (returned by above step), the XPath expression and other details telling the database engine how it should map elements/attributes to columns.
3. Once you are done and no longer need the XML document in memory, call sp_xml_removedocument.

DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc ='
<ImportDetails>
<Date>1/1/2004</Date>
<File>Adobe.msi</File>
</ImportDetails>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '/ImportDetails', 1)
WITH 
([ImportDate] DateTime 'Date',
 [FileName] nvarchar(255) 'File'
)

EXEC sp_xml_removedocument @idoc

The above T-SQL produces following output:

The above sections pretty much covered the XML support provided on the database tier (built into database engine).

SQL Server 2000 also introduced the XML support on middle-tier (Web server in this case) by providing an ISAPI DLL (sqlisapi.dll), XML helper DLL (sqlxmlx.dll), an MMC snap-in called "Configure SQL XML Support in IIS" (SQL IIS Admin.MSC), and a COM API to manage the SQLXML virtual directories.

You can use "Configure SQL XML Support in IIS" MMC snap-in tool to create special SQL Server 2000 virtual directories (that point to a SQL Server database). Once this endpoint is configured, you can then access relational data over HTTP by using URL queries, template file queries, directly access the database objects, and so on (all this is discussed next).

The HTTP request to these special virtual directories is processed by an ISAPI DLL (sqlisapi.dll), which makes use of SQLOLEDB provider and XML helper DLL (sqlxmlx.dll) to talk to the database, and then the ISAPI DLL returns the data to the client.

Note that, this functionality has evolved (and changed) in SQLXML Web releases. In this section, we'll look at HTTP data access as it is available in SQL Server 2000 (without any SQLXML Web release installed). Later in the article, we'll discuss how it works in SQLXML 3.0 SP2.

The very first step towards using HTTP data access functionality is to configure a new virtual directory using “Configure SQL XML Support in IIS" MMC snap-in tool. Let's create a virtual directory named “nwind" and point it to the Northwind sample database.

Click on Start | Programs | Microsoft SQL Server | Configure SQL XML Support in IIS. Right click and select New | Virtual Directory.

• Always connect as a provided SQL Server login (example: connect as sa),
• Always connect as a provided Windows account (example: connect as MyDomain\Joe),
• Use Windows Integrated authentication (use the Windows login of the user browsing the site), or
• Clear text (basic) authentication for SQL Server account (user/client is prompted for SQL Server login ID and password, which is sent to the server as clear text, SSL user is recommended if this option is selected).

For now, let's not configure any Virtual Names or change anything on Advanced tab. Save the virtual directory configuration.

The following examples assume that you have a SQLXML virtual directory configured named nwind and is pointing to the Northwind sample database on SQL Server.

Start Internet Explorer and type the following URL in the address bar:

http://localhost/nwind?sql=SELECT * FROM Employees 
        WHERE LastName Like 'D%25' FOR XML AUTO&root=root

You should get the output similar to as shown in the following screenshot:

• sql – whose value is the query (it can be direct query or a call to stored procedure),
• root – the name of the topmost element in the returned XML (without this the output will not be a well-formed XML).

• xsl – the name of the XSLT stylesheet file that needs to be applied before sending the output to the client
• contenttype – the content type of the output
• template – to execute template (discussed next) directly from the URL (this is not recommended)

In addition to above URL parameters, if the (parameterized) query or stored procedure expects some parameter values, you can pass those also as the URL parameters. Note that URL queries are not just restricted to SELECT statements; you can execute any T-SQL over URL. On one side this opens up unlimited possibilities/extensibility, while on other side this can be biggest security risk. URL queries are hence preferred in a controlled environment (intranet site for example) and are not recommended to be used over Internet.

Another important point to note is that if your query contains characters that have special meaning when used inside a URL, they need to be escaped. For instance, plus (+) needs to be escaped as %2B; forward slash (/) as %2F; question mark (?) as %3F; percent site (%) as %25; hash (#) as %23; and ampersand (&) as %26.

Refer to SQL Server Books Online for security considerations and more details on URL queries. A better (and little more secure) alternative to URL queries is template queries, which is discussed next.

An alternative to URL queries is to create an XML file, called template files, that contains one or more (optionally parameterized) queries and/or call to stored procedure. These XML template files are stored within the virtual directory and are written using the following syntax:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="stylesheet">
    <sql:header>
           One <sql:param> element for each parameter.
    </sql:header>
    [<sql:header> is optional]

     <sql:query>
            One or more queries (one batch)
     </sql:query>
     [You can have more <sql:query> elements, one per batch]

     <sql:xpath-query mapping-schema="AnnotatedXDRFile">
        Specify the XPath query
     </sql:xpath-query>
</ROOT>

The top level element (ROOT in this case) can have any name. You can optionally pass the XSLT stylesheet reference in the template (using sql:xsl attribute on the top element), in which case, the output is transformed before it is returned. For parameterized queries or stored procedure, <sql:param> inside <sql:header> is used to declare and accept the parameter values. You can have zero or more <sql:query> tags, each forming one batch. This tag contains T-SQL query. You can also pass XPath query (using <sql:xpath-query>, in which case annotated XDR schema reference is required.

In order to execute template files, it is required to create a virtual name of type template. SQL Server 2000 supports two other types of virtual names: schema and dbobject. We'll discuss these later. SQLXML 3.0 adds soap virtual name type to provide Web services support. We'll look into this also later. For now, open the virtual directory properties dialog box, click on Virtual Names tab and create a virtual name of type template and call it nwtemplates. You can create a subdirectory and point to that; for simplicity I'll point it to the same directory as the virtual root.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

    <sql:header>
       <sql:param name="recordCount">10</sql:param>
    </sql:header>

     <sql:query>
       SELECT [OrderID], [CustomerID], [EmployeeID]
       FROM [Orders] 
       WHERE [OrderID] < 10248 + @recordCount
       FOR XML AUTO, ELEMENTS
     </sql:query>

</ROOT>

Save the above file as template1.xml under the folder used to create a virtual name (c:\inetpub\wwwroot\nwind on my machine). Now, browse to http://localhost/nwind/nwtemplates/template1.xml and you'll see first 10 records (as the default recordCount parameter value is 10) in the output:

http://localhost/nwind/nwtemplates/template1.xml?recordCount=20

During SQL Server 2000 release, the W3C XML Schema (http://www.w3.org/XML/Schema) was still a work in progress and did not yet achieve the recommendation status. Microsoft's BizTalk server at that time made use of Microsoft's proprietary schema language, called XML Data Reduced or XDR. Microsoft decided to add the support for the same in SQL Server 2000. In other words, SQL Server 2000 contains built-in support for XDR (and it does not support XSD). Later with SQLXML Web releases W3C XML Schema (XSD) support is added, and is the recommended approach. But if you would like to avoid installing and using SQLXML Web release, your only option is to use XDR schema. XDR schema with SQL Server 2000 is primarily used to create the relational mapping and then run XPath queries.

Let's see an example of how XDR schema mapping and XPath queries work in SQL Server 2000.

Click on Start | Programs | Microsoft SQL Server | Configure SQL XML Support in IIS. Open the Properties dialog for nwind virtual root that we created earlier. Using the Virtual Names tab, create a new virtual named xdrschema of type schema and for simplicity let's point it to the same directory as the virtual root (c:\inetpub\wwwroot\nwind in my case):

Save the following annotated XDR schema file, sample1.xdr, in the virtual name folder (c:\inetpub\wwwroot\nwind in my case):

<?xml version="1.0" encoding="utf-8"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:datatypes"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<ElementType name="Shippers" sql:relation="Shippers" >
    <AttributeType name="ID" />
    <AttributeType name="Name" />
    <AttributeType name="Phone" />

    <attribute type="ID" sql:field="ShipperID" />
    <attribute type="Name" sql:field="CompanyName" />
    <attribute type="Phone" />
</ElementType>
</Schema>

Start Internet Explorer and browse to:

http://localhost/nwind/xdrschema/sample1.xdr/Shippers?root=root

The above URL contains IIS server name (localhost), SQLXML virtual root (nwind), schema virtual name (xdrschema), name of the XDR file (sample1.xdr) and the XPath expression (Shippers). It produces the following results:

http://localhost/nwind/xdrschema/sample1.xdr/Shippers[@ID='2']?root=root

The HTTP request is intercepted by the SQLXML ISAPI DLL (sqlisapi.dll), which uses the annotated XDR schema and XPath expression to formulate the database query, sends it to SQL Server using SQLOLEDB, makes use of XML helper file (sqlxmlx.dll) and returns the XML to the client.

In above example, we run the XPath query over the URL. You can also execute XPath queries using template files. Refer to SQL Server Books Online for more details on this.

It is interesting to note that when you execute XPath queries along with mapping schema, internally it ends up in a FOR XML EXPLICIT query. So, if you run the above mapping schema XPath URL queries, you can use SQL Profiler to see what EXPLICIT queries are run internally - a good way to learn FOR XML EXPLICIT query mode.

In all the examples so far, we were sending the HTTP requests using Internet Explorer. You can very well programmatically invoke the URL queries or template queries using any HTTP client API like WinInet, WinHTTP, XMLHTTP, or ServerXMLHTTP, and get back the XML.

As an exercise, read and try out dbobject virtual name, read more about creating and customizing XML views using XDR schemas, and learn about other options and how they work in Configure SQL XML Support in IIS tool.

So much for the XML and HTTP support built into SQL Server 2000. Let's now see, what new/improved features are available in SQLXML 3.0 SP2 Web release.

As mentioned earlier, to keep up with the evolving XML and Web services standards, Microsoft decided to make Web releases of SQLXML on http://msdn.microsoft.com/sqlxml site. In last three years, three main versions of SQLXML have been released (and various beta versions and services packs). The current version SQLXML 3.0 SP2 can be downloaded from http://msdn.microsoft.com/sqlxml.

Following table summarizes the major features as they were introduced in each Web release:

Complete discussion on all these features is out of the scope of this article. I'll present one simple example and explanation of some of the major features outlined above. Let's begin be having a quick look at Updategrams:

Updategrams allow inserting, deleting, and updating the relational data through XML. Like template files (discussed above in Template Queries), Updategrams are also XML files stored under SQLXML virtual directory. Updategrams XML files consist of special tags that describe what the data looks like initially and how it should be once an Updategram is executed. The XML to relational mapping is either implicit (same named elements/attributes) or explicit (via an annotated XSD or XDR schema).

There are primarily four ways to execute Updategrams:

• Save the Updategram as a template (disk) file and execute it (send a GET HTTP request for the Updategram file)
• Send an HTTP POST request, and send Updategram text as the POST data
• Use ADO or OLEDB (using a Command dialect) to apply an Updategram
• Use SQLXML Managed Classes to apply an Updategram

In this article, we'll create an Updategram as a template disk file, and simply browse to it using Internet Explorer, to run the Updategram.

Let's look at a very simple example of how Updategrams look like:

<?xml version="1.0" encoding="utf-8" ?> 
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
    <updg:sync updg:nullvalue="dbnull">

        <updg:before>
            <Customers CustomerID="BOLID" />
        </updg:before>
    
        <updg:after>
            <Customers Phone="111-111-1111" Fax="dbnull"/>
        </updg:after>
        
    </updg:sync>
</ROOT>

An Updategram consists of one or more <updg:sync> blocks, indicating a database transaction. Each such blocks consists of one or more sets of <updg:before> and <updg:after> blocks. These two blocks, and their contents, actually decide the database operation (insert, update, delete) that should happen.

If you skip or do not write anything in the <updg:before> block, but writing some XML under <updg:after>, this indicates an INSERT operation (there was nothing before, here is something that should be after). If you omit or do not write anything in the <updg:after> block, but writing some XML under <updg:before>, this indicates an DELETE operation (there was something before, nothing should be after). If you write some XML in both the blocks, it means an UPDATE operation.

The above example, hence, performs a database update. For Customer with CustomerID equals BOLID, it changes the Phone to 111-111-1111 and sets the Fax to NULL (note how updg:nullvalue special attribute is placed on the <updg:sync> block).

Save the above text in an XML file, Updg1.xml, in a folder which we used earlier to create a template type virtual name (c:\inetpub\wwwroot\nwind in my case). And then browse to

http://localhost/nwind/nwtemplates/Updg1.xml

If it returns just the ROOT element, that means, Updategram executed successfully. Run following query in SQL Query Analyzer and verify that the Phone and Fax fields have a new value.

SELECT [Phone], [Fax] FROM [Customers] WHERE [CustomerID] = 'BOLID' 

The above examples used the implicit mapping (as the element/attribute names match the table/column names). There is a lot to learn on Updategrams, like how the identity columns work, how to use mapping schema, executing multiple updates in a single or multiple transactions, error handling, calling Updategrams from ADO code, POSTing an updategram over HTTP, updating multiple tables, handling special characters, dealing with data types conversion issues, generating GUIDs, passing parameters, and so on.

Refer to SQLXML documentation for complete details on Updategrams.

Introduced in SQLXML 1.0, this COM component can be used to efficiently load (insert) XML data into SQL Server. To bulk import the XML data, in addition to source XML document, you'll also need XDR or XSD schema. The load operation can be executed in a transaction or without a database transaction.

Let's look at an example. First let's look at sample XML document and XSD schema. And then we'll use the XML Bulk Load component from a DTS ActiveX script:

XSD Schema for the source XML (c:\shippers.xsd):

<?xml version="1.0" encoding="utf-8" ?> 
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    
    <xsd:element name="NWShippers" sql:is-constant="true">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="Shipper" 
                    sql:relation="Shippers">
                    <xsd:complexType>
                        <xsd:attribute name="CompanyName"
                            type="xsd:string"
                            sql:field="CompanyName"
                            sql:datatype="nvarchar(40)" />
                        <xsd:attribute name="Phone"
                            type="xsd:string"
                            sql:field="Phone"
                            sql:datatype="nvarchar(24)" />
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>

</xsd:schema>

Sample Source XML Document (c:\shippers.xml):

<?xml version="1.0" encoding="utf-8" ?> 
<NWShippers>
    <Shipper CompanyName="a" Phone="111" />
    <Shipper CompanyName="b" Phone="222" />
    <Shipper CompanyName="c" Phone="333" />
    <Shipper CompanyName="d" Phone="444" />
    <Shipper CompanyName="e" Phone="555" />
    <Shipper CompanyName="f" Phone="666" />
</NWShippers>

DTS ActiveX Script Task VBScript:

Function Main()
    Dim objSQLXMLBulkLoad

    Set objSQLXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

    objSQLXMLBulkLoad.ConnectionString = _
        "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=sa;DATABASE=NorthWind;"

    objSQLXMLBulkLoad.ErrorLogFile = "C:\Shippers.log"
   
    objSQLXMLBulkLoad.KeepIdentity = False
    
    objSQLXMLBulkLoad.Execute "C:\Shippers.xsd", "C:\Shippers.xml"
    
    Set objSQLXMLBulkLoad = Nothing

    Main = DTSTaskExecResult_Success
End Function

Using SQL Server Enterprise Manager, create a new empty DTS package. In the Designer Window, drag and drop an ActiveX Script Task, paste the above code, save the script and run the step. Look at the Shippers table and make sure that XML file is imported fine. If not, review the c:\shippers.log file for errors.

For one more example, see my article, Importing XML into SQL Server 2000, at http://www.PerfectXML.com/articles/xml/importxmlsql.asp#3.

As an exercise, try the SQLXML Bulk Load from .NET code (using COM-interop).

Refer to SQLXML documentation for complete details on XML Bulk Load functionality.

SQLXML includes a .NET assembly Microsoft.Data.SqlXml.dll, which contains three main classes, SqlXmlCommand, SqlXmlParameter, and SqlXmlAdapter, in the Microsoft.Data.SqlXml namespace. These classes help in working with XML features in SQL Server and SQLXML, for example, to execute a FOR XML query, to call a URL or template query, execute an Updategram, and so on.

To use these classes, you need to add reference to Microsoft.Data.SqlXml.dll and then import Microsoft.Data.SqlXml namespace.

Create a C# console application, add reference to SQLXML Managed Classes assembly, and write the following code:

using System;
using System.IO;
using System.Data;
using Microsoft.Data.SqlXml;

class SQLXMLDemo
{
   [STAThread]
   static void Main(string[] args)
   {
      SqlXmlCommand comm = new SqlXmlCommand(
         "PROVIDER=SQLOLEDB;SERVER=.;UID=sa;PWD=sa;DATABASE=Northwind;");
      
      comm.CommandType = SqlXmlCommandType.Sql;
      comm.CommandText = "SELECT * FROM [Customers] FOR XML AUTO, ELEMENTS";
      comm.OutputEncoding = "UTF-8";
      comm.RootTag = "NWCustomers";

      FileStream xmlFile = 
         new FileStream("c:\\nwCustomers.xml", FileMode.Create,              
               FileAccess.Write);

      comm.ExecuteToStream(xmlFile);

      xmlFile.Close();
   }
}

The biggest highlight in SQLXML 3.0 is the SOAP/Web services support. This feature allows turning stored procedures, user defined functions, and templates into Web service methods, within seconds. With just few clicks, you can expose a stored procedure as a Web service method.

As mentioned earlier, a new Virtual Name of type soap was introduced in SQLXML 3.0 to provide Web services support. Note that if you created the SQLXML virtual directory using SQL Server 2000’s “Configure SQL XML Support in IIS” tool, you need to first upgrade it to SQLXML 3.0. To do this, click on Start | Programs | SQLXML 3.0 | Configure IIS Support and then open the virtual directory Properties dialog (double click on it), and on the Version 3 tab, click Upgrade to version 3.

On the Virtual Names tab, select the <New virtual name> option in the list, select the Type as soap, name it nwSOAP, and point it to the same directory as virtual root or a subdirectory under it. Save the virtual name.

The Configure button will now be enabled. Clicking on it brings the following dialog box allowing you to map stored procedures or templates as Web methods:

All the above setup does is to create two files: a .ssc file, which contains the Web method details and an .wsdl file, which is a standard Web services contract document. Browse to the following URL and make sure that the WSDL document comes up fine:

http://localhost/nwind/nwsoap?wsdl

The above URL includes the IIS server name, SQLXML virtual directory name, soap virtual name, and ?wsdl at the end.

As an exercise, write a Web service client application using .NET for the above Web method.

SQL Server 2000 introduced the XML support, which was extended using SQLXML Web releases. This article presented an overview of all the XML and .NET features in SQL Server 2000 and SQLXML 3.0. The article began with discussion on XML composition and decomposition using FOR XML and OPENXML, respectively; then we looked at HTTP data access support in SQL Server 2000. The later part of the article described SQLXML Web releases, and introduction to topics such as Updategrams, XML bulk load, and SOAP support. Refer to SQL Server and SQLXML documentation, and links in the following sections to learn more about these features.

There are many things that SQL Server 2000 and SQLXML (as of 3.0 SP2) does not provide, such as native XML storage (XML data type), XQuery support, complete XPath support, and so on. Yukon addresses these and more. Continue exploring this Web site to learn about XML and .NET support in Yukon.

• MSDN SQLXML Home
  http://msdn.microsoft.com/sqlxml
• SQL Server 2000: New XML Features Streamline Web-centric App Development
  http://msdn.microsoft.com/msdnmag/issues/0300/sql/
• MSDN Magazine Article: Build Data-Driven Web Services with Updated XML Support for SQL Server 2000
  http://msdn.microsoft.com/msdnmag/issues/02/05/sqlxml3/default.aspx
• A SQL Server XML and ASP.NET Runtime Application
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dndotnet/html/csharpsqlxmlhttp.asp
• Distributing Enterprise Data via XML with SQL Server 2000 and Excel 2002
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_excelsqlxml.asp
• SQL Server XML KB Articles

• Refer to SQL Server 2000 Books Online for complete details on XML and HTTP support built into the product.
• If you have installed SQLXML 3.0, refer to SQLXML 3.0 SP2 (online available here) documentation for complete details on SQLXML 3.0 features.
• Download SQL Server 2000 Web Services Toolkit and go through the code and white papers included in it.
• Read my article XML and HTTP Feature Changes in Yukon.
• Read other articles on this site to find out what is coming up in Yukon as far as XML and .NET is concerned.