2.5 XML in Microsoft SQL Server 2000

---

Microsoft SQL Server 2000 ships with a number of XML features in the box, making the process of turning relational data into XML reasonably straightforward.

2.5.1 Data Access from a URL

Transact SQL (T-SQL) statements can now be submitted directly to SQL Server from a Web site URL (uniform resource location-the www address of a Web site), since SQL Server ships with a set of SQL Internet Server API (ISAPI) extensions. A typical use of this would be to submit a query or execute a stored procedure as part of the Web site address.

A typical example could be

http://IISServer/  pubs?sql=SELECT+*+FROM+Authors+FOR+XML+RAW&root=root  

By using FOR XML RAW , we are actually returning the customer's data as an XML document rather than a SQL Server record set. This will be explained later.

To overcome the problem of limited character space in a URL, and the security risk of allowing direct query access to SQL Server, the better way to implement this is to use XML templates. The templates are used to store the T-SQL statements and XPATH queries. For example:

 http://IISServer/pubs/Templates/templatefile.xml  

This has the added benefit of securing the detail of the T-SQL statements, thereby providing a layer of basic security.

A typical template file would look like this:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">       <sql:query>         SELECT *        FROM Authors        FOR XML RAW      </sql:query>  </ROOT>   

Note the use of the namespace.

The T-SQL SELECT statement has now been extended to include additional keywords in support of three XML modes that determine the nature or serialization of the retrieved XML data.

RAW

RAW takes each row returned from the query and places it within a generic element tag <row/> For example, this RAW output has au_lname and title contained in the <row/> tags.

<?xml version="1.0" ?>  <ROOT>    <row au_lname="England" title="The SQL Server Magical  Database Guide" />     <row au_lname="Blotchet-Halls" title="Fifty Years in  Buckingham Palace Kitchens" />     <row au_lname="Carson" title="But Is It User Friendly?" />    <row au_lname="DeFrance" title="The Gourmet Microwave" />  </ROOT>  

This is the most basic form of output with limited use.

AUTO

AUTO builds what is called a nested XML tree with XML elements built from the tables included in the SELECT statement.

<?xml version="1.0" ?>  <ROOT> <authors au_lname="England">   <titles title="The SQL Server Magical Database Guide" />  </authors> <authors au_lname="Blotchet-Halls">   <titles title="Fifty Years in Buckingham Palace Kitchens" />  </authors> <authors au_lname="Carson">   <titles title="But Is It User Friendly?" />  </authors> <authors au_lname="DeFrance">   <titles title="The Gourmet Microwave" />  </authors> </ROOT> 

EXPLICIT

EXPLICIT is more complicated, but allows the query to specify the appropriate XML nesting and the precise nature of the XML structure. This is far more useful to the developer, although the understanding required of XML is a bit deeper.

---