Chapter 31 - Exposing SQL Server Data to the Web with XML

Extensible Markup Language (XML) is a meta-markup language that provides a format for describing structured data. Unlike HTML, XML does not handle the presentation of data. Instead, the tags used to encompass the data are used to describe the data. This allows for a simplified passing of data between two programs. Microsoft® SQL Server™ 2000 introduces a set of features that enable XML support. This chapter introduces some of the XML features of SQL Server 2000, demonstrating the speed and ease of configuring SQL Server for XML support.

For information about the latest updates relating to XML support, see the XML Developer Center at https://msdn2.microsoft.com/xml/default.aspx.

Generating XML with the SELECT 1050404404Statement

Cc917658.spacer(en-us,TechNet.10).gif Cc917658.spacer(en-us,TechNet.10).gif

It is possible to retrieve XML formatted results using the Transact-SQL SELECT statement and the FOR XML clause. The modes within the FOR XML clause are:

  • FOR XML RAW. Returns each row of the query result set in a generic row element with each non-null column as an attribute. 

  • FOR XML AUTO. Returns the query results as nested elements corresponding to the tables in the FROM clause of the select statement. By default, the AUTO mode maps the selected table columns as attributes of the element; however if the ELEMENTS option is enabled, the selected table columns become sub-elements of the table element. 

  • FOR XML EXPLICIT. Allows the query more control over the formatting of the returned XML result set. The query is designed to explicitly define the XML document returned; therefore, the author of the query becomes responsible for creating a well-formed XML document. This option allows for the most control over the query result set. 

To generate an XML formatted result set 

  1. Start SQL Query Analyzer, and then connect to the Northwind database. 

  2. Execute the following query: 

    SELECT * from CUSTOMERS FOR XML RAW
    

SQL Server returns the result set in a single column named in part with a Globally Unique Identifier (GUID). Unlike the XML Technology preview, the results are not wrapped in the <root></root> tags that are needed for a properly formatted XML document. This allows multiple queries to produce one XML document.

Generating XML over the Internet

Cc917658.spacer(en-us,TechNet.10).gif Cc917658.spacer(en-us,TechNet.10).gif

To exploit the full functionality of SQL Server XML support over the Internet, download and install the latest Web Release for SQL Server 2000 XML support from the Developer Center on MSDN. For more information, see https://msdn2.microsoft.com/xml/default.aspx.

Before accessing SQL Server over the Internet, a virtual directory must be set up on the computer running Internet Information Services (IIS) specifically for the XML queries.

To enable IIS for SQL Server XML support 

  1. On the Start menu, point to Program Files, click Microsoft SQL Server, and then click Configure SQL XML Support in IIS

  2. Expand your server, right-click the Default Web Site folder, select New, and then select Virtual Directory.

  3. On the General tab of the properties page, type XMLTest for the virtual directory name. 

  4. For Local Path, type C:\ reskit\xmltest

  5. On the Security tab, enter a user name and password with administrative permissions in the Northwind database. 

  6. On the Datasource tab, enter the database server name, and then enter Northwind as the Database name

  7. On the Settings tab, select Virtual URL Queries and Template Queries

  8. On the Virtual Names tab, click New, and then add a new virtual name of queries with a type of template and a path to C:\reskit\xmltest. 

  9. Click OK to save your settings, and then close the Virtual Directory Management Tool. 

Retrieving XML Formatted Data from SQL Server

You can query SQL Server 2000 and retrieve an XML formatted document in any of the following ways:

  • Send SQL Server a query string in the HTTP request (this can be either a Transact-SQL SELECT statement or an X-Path query). 

    Important security considerations should be considered when exposing your database to queries through the browser. Any user can execute a query within the user context of the username specified in the virtual root. If you choose to allow this type of access, make sure that you apply the appropriate permissions within the database for this user. 

  • Create template files that contain Transact-SQL or X-Path queries and attach XML Template (.xsl) files to format the XML document that is returned. (This is generally easier and more secure than sending a query string.) 

To Retrieve XML using a Transact-SQL statement in a URL 

  1. In the Microsoft Internet Explorer address bar, type the following query, ensuring that the query is entered without any line breaks. 

    https://localhost/xmltest?sql=SELECT+contactname,+phone+FROM+Customers+FOR+XML+
    

raw&root=root

  1. With your cursor still in the address bar, press ENTER to execute the query. 

These results have been truncated for readability.

<root>
<row contactname="Maria Anders" phone="030-0074321" />
<row contactname="Ana Trujillo" phone="(5) 555-4729" />
<row contactname="Antonio Moreno" phone="(5) 555-3932" />
<row contactname="Thomas Hardy" phone="(171) 555-7788" />
<row contactname="Christina Berglund" phone="0921-12 34 65" />
<row contactname="Hanna Moos" phone="0621-08460" />
…
</root>

The contactname and phone columns named in the query are attributes of the row element. By using a JOIN and the FOR XML AUTO clause, the results appear as XML elements to form a more hierarchical structure.

To execute a query that generates AUTO formatted XML results 

  1. In Microsoft Internet Explorer, copy the following query into the address bar, ensuring that the query is entered without any line breaks. 

    https://localhost/xmltest?sql=SELECT+customers.CustomerID,OrderID,OrderDate+FROM+Custo
    

mers+INNER+JOIN+Orders+ON+customers.CustomerID=orders.CustomerID+ORDER+BY+customers.C ustomerID,OrderID+FOR+XML+AUTO&root=root

  1. With your cursor still in the address bar, press ENTER to execute the query. 

These results have been truncated for readability.

- <root>
- <Customers CustomerID="ALFKI">
<Orders OrderID="10643" OrderDate="1997-08-25T00:00:00" /> 
<Orders OrderID="10692" OrderDate="1997-10-03T00:00:00" /> 
<Orders OrderID="10702" OrderDate="1997-10-13T00:00:00" /> 
<Orders OrderID="10835" OrderDate="1998-01-15T00:00:00" /> 
<Orders OrderID="10952" OrderDate="1998-03-16T00:00:00" /> 
<Orders OrderID="11011" OrderDate="1998-04-09T00:00:00" /> 
</Customers>
- <Customers CustomerID="ANATR">
<Orders OrderID="10308" OrderDate="1996-09-18T00:00:00" /> 
<Orders OrderID="10625" OrderDate="1997-08-08T00:00:00" /> 
<Orders OrderID="10759" OrderDate="1997-11-28T00:00:00" /> 
<Orders OrderID="10926" OrderDate="1998-03-04T00:00:00" /> 
</Customers>
…
</root>

You can expand or collapse customer information to view the Orders elements of a particular customer.

Reserved Characters

There is a set of reserved characters for both URLs and XML documents. The following table lists each special character, its function, and the hexadecimal value that can be used to replace the character. Queries in a URL must replace the special characters that are used in URLs with their Hex Values.

Special Characters in URLs 

Character

Use

Hex value

+

Indicates a space

%20

/

Separates directories and subdirectories

%2f

?

Separates the URL and the parameters

%3f

%

Specifies special characters

%25

#

Indicates bookmarks

%23

&

Separator between URL parameters

%26

Queries in XML Template documents must properly encode the special characters used in XML with the values listed in the following table.

Special Characters in XML 

Special character

User

Entity encoding

<

Begins a tag

>

>

Ends a tag

<

"

Quotation mark

"

'

Apostrophe

&apos;

&

Ampersand

&

To execute an XML generating Transact-SQL query with special characters 

  1. In Microsoft Internet Explorer, enter the following query into the address bar, ensuring that the query is entered without any line breaks. 

    https://localhost/xmltest?sql=SELECT+'<ROOT>';SELECT+DISTINCT+ContactTitle+FROM+Custom
    

ers+WHERE+ContactTitle+LIKE+'Sa%25'+ORDER+BY+ContactTitle+FOR+XML+AUTO;SELECT+'</ROOT >'

  1. With your cursor still in the address bar, press ENTER to execute the query. 

In addition to the use of special characters, this query also manually generated the <ROOT></ROOT> tags by explicitly naming them.

Stored procedures can also be executed from the URL. They can be called either with the Transact-SQL syntax EXECUTE sp_name or with the ODBC syntax 1050404405{call sp_name}. The following example shows how a stored procedure is created to dynamically publish the Northwind product catalog to third parties.

To execute a stored procedure using Internet Explorer 

  1. Start SQL Query Analyzer, and then connect to your server. 

  2. Ensure that the current Query window is pointing to the Northwind database, and then execute the following: 

    CREATE PROCEDURE sp_rk_xmlCatalog
    

as SELECT Category.CategoryName as 'Category', product.productName as 'Product', Product.UnitPrice as 'Price' FROM Categories Category INNER JOIN Products Product ON Category.CategoryID = Product.CategoryID Order by Category.CategoryName, Product.ProductName FOR XML AUTO

  1. In Microsoft Internet Explorer, enter the following query into the address bar, ensuring that the query is entered without any line breaks. 

    https://localhost/xmltest?sql=EXECUTE+sp_rk_XMLCatalog&root=root
    
  1. With your cursor still in the address bar, press ENTER to execute the query. 

A list of products in the Northwind database will appear, sorted by category. These results are truncated to enhance readability

<?xml version="1.0" encoding="utf-8" ?> 
- <root>
- <Category Category="Beverages">
<Product Product="Chai" Price="80" /> 
<Product Product="Chang" Price="19" /> 
<Product Product="Chartreuse verte" Price="80" /> 
<Product Product="Côte de Blaye" Price="263.5" /> 
<Product Product="Guaraná Fantástica" Price="4.5" /> 
<Product Product="Ipoh Coffee" Price="46" /> 
<Product Product="Lakkalikööri" Price="80" /> 
<Product Product="Laughing Lumberjack Lager" Price="14" /> 
<Product Product="Outback Lager" Price="15" /> 
<Product Product="Rhönbräu Klosterbier" Price="7.75" /> 
<Product Product="Sasquatch Ale" Price="14" /> 
<Product Product="Steeleye Stout" Price="80" /> 
</Category>
- <Category>
…
</Category>
…
</root>

This stored procedure can be modified to take a parameter to allow for queries against the database for specific categories. A default parameter can be added to the stored procedure defined above, sp_rk_xmlCatalog. Next, the procedure can be called from Internet Explorer.

  1. Execute the following code in SQL Query Analyzer. 

    ALTER PROCEDURE sp_rk_xmlCatalog
    

@CategoryName nvarchar(30) = '%' as SELECT Category.CategoryName as 'Category', product.productName as 'Product', Product.UnitPrice as 'Price' FROM Categories Category INNER JOIN Products Product ON Category.CategoryID = Product.CategoryID WHERE Category.CategoryName like @CategoryName Order by Category.CategoryName, Product.ProductName FOR XML AUTO

  1. In Microsoft Internet Explorer, enter the following query into the address bar, and then press ENTER. 

    https://localhost/xmltest/?sql=EXECUTE+sp_rk_xmlCatalog+@CategoryName="Confections"&root=root
    

Only the products in the Confections category should be listed.

- <root>
- <Category Category="Confections">
<Product Product="Chocolade" Price="12.75" /> 
<Product Product="Gumbär Gummibärchen" Price="31.23" /> 
<Product Product="Maxilaku" Price="20" /> 
<Product Product="NuNuCa Nuß-Nougat-Creme" Price="14" /> 
<Product Product="Pavlova" Price="17.45" /> 
<Product Product="Schoggi Schokolade" Price="43.9" /> 
<Product Product="Scottish Longbreads" Price="12.5" /> 
<Product Product="Sir Rodney's Marmalade" Price="81" /> 
<Product Product="Sir Rodney's Scones" Price="10" /> 
<Product Product="Tarte au sucre" Price="49.3" /> 
<Product Product="Teatime Chocolate Biscuits" Price="9.2" /> 
<Product Product="Valkoinen suklaa" Price="16.25" /> 
<Product Product="Zaanse koeken" Price="9.5" /> 
</Category>
</root>
XML Templates

As queries grow in complexity, it becomes impractical to send them through URLs. Furthermore, you might not want to expose the queries in the address bar of the browser where they can be edited by third parties. Instead, queries can be stored in XML templates. Templates are well-formed XML documents that contain one or more SQL statements or X-Path queries. The XML virtual root for IIS must be specifically configured to use templates before they will work on your system.

To enable the XML virtual root within IIS to use templates, create a virtual name within the Virtual Directory that is enabled for XML templates.

To allow IIS virtual roots to use template files 

  1. On the Start menu, point to Programs, click Microsoft SQL Server, and then click Configure SQL XML Support in IIS

  2. Expand your server, and then click Default Web Site

  3. Right-click the XMLTest virtual directory, and then click Properties

  4. On the Settings tab, ensure that Allow template queries is selected. 

  5. On the Virtual Names tab, click New to add a virtual name for the templates folder. 

    Use a virtual name for templates that is type Template, and then set the path to C:\reskit\xmltest

  6. Click Save, and then close the IIS Virtual Directory Manager for SQL Server. 

The following steps show you how to create a simple XML template and access the result set using Internet Explorer.

To create an XML template that executes a simple Transact-SQL query 

  1. Paste the following code into your text editor: 

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

<sql:query> SELECT SupplierID, CompanyName, ContactName, Phone FROM suppliers ORDER BY CompanyName FOR XML AUTO </sql:query> </ROOT>

  1. Save the file as C:\xmltest\suppliers.xml

  2. In Microsoft Internet Explorer, enter the following query into the address bar, and then press ENTER. 

    https://localhost/xmltest/templates/suppliers.xml
    

The URL includes the \templates directory even though the template is stored directly in the xmltest directory. This is because it is mapped to the suppliers.xml template through the templates virtual name that was created earlier.

Using EXPLICIT Mode

EXPLICIT mode brings an additional level of complexity to a query, but allows for much more control over the XML document that is generated by the query. EXPLICIT mode requires that the query produce a result set in the universal table format. The universal table fully describes the resulting XML document.

The following table demonstrates the logical structuring of the data within a query that returns Products, Categories, and Product Details such as Price and Supplier.

Tag

Parent

Category!1 !name

Product!2 !Name

Product!2 !Supplier!Element

Product!2 !Price!Element

1

NULL

Beverages

NULL

NULL

NULL

2

1

NULL

Chai

Exotic Liquids

80

2

1

NULL

Chang

Exotic Liquids

19

1

NULL

Condiments

NULL

NULL

NULL

2

1

NULL

Aniseed Syrup

Exotic Liquids

10

The column names are encoded using XML generic identifiers and attribute names. These names are specified as:

ElementName!TagNumber!AttributeName!Directive

To learn more about this syntax and the EXPLICIT mode, see SQL Server Books Online.

To return the Northwind store catalog using EXPLICIT mode 

  1. Paste the following code into your text editor: 

    SELECT 
    

1 as 'Tag', null as 'Parent', Category.CategoryName as 'Category!1!Name', null as 'Product!2!Name', null as 'ProductDetail!3!Supplier', null as 'ProductDetail!3!Price', null as 'ProductDetail!3!NumberSold' FROM categories Category

UNION ALL SELECT 2, 1, Category.CategoryName, Product.ProductName, null, null, null FROM categories Category INNER JOIN products Product on Category.CategoryID = Product.CategoryID

UNION ALL SELECT 3, 2, Category.CategoryName, Product.ProductName, null, Product.UnitPrice, null FROM categories Category INNER JOIN products Product on Category.CategoryID = Product.CategoryID INNER JOIN suppliers s on product.supplierid = s.supplierid

UNION ALL SELECT 3, 2, Category.CategoryName, Product.ProductName, s.CompanyName, null, null FROM categories Category INNER JOIN products Product on Category.CategoryID = Product.CategoryID INNER JOIN suppliers s on product.supplierid = s.supplierid

UNION ALL SELECT 3, 2, Category.CategoryName, Product.ProductName, null, null, sum(quantity) FROM categories Category INNER JOIN products Product on Category.CategoryID = Product.CategoryID INNER JOIN suppliers s on product.supplierid = s.supplierid LEFT JOIN [order details] od ON product.productid = od.productid group by category.categoryname,product.productname,s.companyname ORDER BY 'Category!1!Name', 'Product!2!Name', 'ProductDetail!3!Supplier', 'ProductDetail!3!Price', 'ProductDetail!3!NumberSold' FOR XML EXPLICIT

  1. Save the file as Catalog.xml

  2. In Microsoft Internet Explorer, enter the following query into the address bar, and then press ENTER. 

    https://localhost/xmltest/templates/catalog.xml
    

The results are hierarchical; each piece of information is stored as an XML element instead of an attribute to an element. Elements on the same level, such as those owned by the Product Element, can have different attributes, such as Price and Supplier.

- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
- <Category Name="Beverages">
- <Product Name="Chai">
<ProductDetail Price="80" /> 
<ProductDetail Supplier="Exotic Liquids" /> 
</Product>
- <Product Name="Chang">
<ProductDetail Price="19" /> 
<ProductDetail Supplier="Exotic Liquids" /> 
</Product>
…
</Category>
…
</root>

XSL Style Sheets 

If the XML formatted information will be displayed directly to the user, you can use XSL Style Sheets (XSL) to format the information. You can also use XSL to filter the XML document so that only certain information is displayed.

For example, if you are publishing a list of all of your employees, you might want to display their phone numbers internally and not externally to the public. The following example creates an XML template that returns employee information and two XSL style sheets, one to format information for external use and another for internal use.

Save the following XML template as EmpList.xml.

<?xml version ='1.0' encoding='UTF-8'?> 
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'> 
<sql:query> 
SELECT FirstName, LastName, Extension 'Extension' FROM Employees FOR XML AUTO 
</sql:query> 
</root>

View the data from the XML template in Internet Explorer by loading https://localhost/xmltest/templates/emplist.xml. The attributes in the Employee Element are: FirstName, LastName, and Extension.

The next example creates a style sheet to format the results into an HTML document. The style sheet performs recursion on the returned XML document for all Employee elements and creates a new row in an HTML table to store the attributes.

Save the following XSL style sheet in your XMLtest directory as empInternal.xsl.

<?xml version='1.0' encoding='UTF-8'?> 
<xsl:stylesheet xmlns:xsl="https://www.w3.org/1999/XSL/Transform" version="1.0"> 

<xsl:template match = '*'> 
<xsl:apply-templates /> 
</xsl:template> 
<xsl:template match = 'Employees'> 
<TR> 
<TD><xsl:value-of select = '@FirstName' /></TD> 
<TD><B><xsl:value-of select = '@LastName' /></B></TD>
<TD><xsl:value-of select = '@Extension' /></TD>
</TR> 
</xsl:template>
<xsl:template match = '/'> 
<HTML> 
<HEAD> 
<STYLE>th { background-color: #CCCCCC }</STYLE> 
</HEAD> 
<BODY> 
<TABLE border='1' style='width:300;'> 
<TR><TH colspan='3'>Employees</TH></TR> 
<TR><TH >First name</TH><TH>Last name</TH><TH>Extension</TH></TR> 
<xsl:apply-templates select = 'root' /> 
</TABLE> 
</BODY> 
</HTML> 
</xsl:template> 
</xsl:stylesheet>

View the formatted results of the XML template in Internet Explorer by loading https://localhost/xmltest/templates/emplist.xml?xsl=empInternal.xsl. The style sheet is applied to the XML template from the URL. Different versions of this Web page can be displayed to different types of users by applying different style sheets to the same XML.

The next example creates an XSL Style Sheet to post employee names externally, and without the phone numbers.

Save the following style sheet as EmpInternal.xsl.

<?xml version='1.0' encoding='UTF-8'?> 
<xsl:stylesheet xmlns:xsl="https://www.w3.org/1999/XSL/Transform" version="1.0"> 

<xsl:template match = '*'> 
<xsl:apply-templates /> 
</xsl:template> 
<xsl:template match = 'Employees'> 
<TR> 
<TD><xsl:value-of select = '@FirstName' /></TD> 
<TD><B><xsl:value-of select = '@LastName' /></B></TD>
</TR> 
</xsl:template>
<xsl:template match = '/'> 
<HTML> 
<HEAD> 
<STYLE>th { background-color: #CCCCCC }</STYLE> 
</HEAD> 
<BODY> 
<TABLE border='1' style='width:300;'> 
<TR><TH colspan='3'>Employees</TH></TR> 
<TR><TH >First name</TH><TH>Last name</TH></TR> 
<xsl:apply-templates select = 'root' /> 
</TABLE> 
</BODY> 
</HTML> 
</xsl:template> 
</xsl:stylesheet>

View the new results in Internet Explorer by loading https://localhost/xmltest/templates/emplist.xml?xsl=empExternal.xsl.

Although the same XML file was used in this example, the results do not show the extension column.

For more information about XML and SQL Server see the following:

Cc917658.spacer(en-us,TechNet.10).gif