[ ABOUT THE AUTHOR ]

Over the past few years, software developers have used various kinds of technologies to retrieve data from relational databases. We have used DAO, RDO and most recently ADO with lots of advantages and also some drawbacks. Now that we have ADO.NET which comes with Microsoft .NET Framework, we have a lot more advantages than other technologies. Many a time we are faced with a requirement return XML as a result of our programming. SQL Server 2000 is the first Microsoft DBMS to fully support XML. Transact-SQL allows us to read and write data in the XML format and we can also use SQL Server XML extensions to retrieve data from the database as an XML tree. In this article I concentrate on writing about the FOR XML clause in both SQL Server versions, 2000 and 2005. If you use SQL Server 2005, SQL Server Management Studio allows you to generate XML easily and shows it using colors. But if you use SQL Server 2000’s SQL Query Analyzer it does not give the XML as a separate document when you clicked on the results grid nor does it show any colors. See the below images to get an idea about the differences between the two versions.

First, I expect to write about query writing first and after use this program later. I assume you have a fair understanding of XML. If you do not, you can learn about it and related technologies at http://www.w3schools.com/. I have used the ‘Northwind’ database which comes with SQL Server 2000 for the examples. If you do not have it, it can be downloaded.

Usually, SELECT statements returns a row set. You can optionally retrieve the result of a SELECT query as an XML by specifying the FOR XML clause in the query. The FOR XML clause uses a few different modes:

• RAW

This is found in both versions (2000 and 2005). This mode generates a single <row> element per record in the resultset.

SELECT ContactName, CompanyName, ContactTitle, Phone
FROM [Customers]
WHERE [CustomerID]= 'ALFKI'
FOR XML RAW

Result:

<row ContactName="Maria Anders" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Phone="030-0074321" />

• AUTO

This option simply treats the table name as the element and column names as attributes. This is also found in both versions (2000 and 2005).

SELECT ContactName, CompanyName, ContactTitle, Phone
FROM [Customers]
WHERE [CustomerID]='ALFKI'
FOR XML AUTO

Result:

<Customers ContactName="Maria Anders" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Phone="030-0074321" />

• EXPLICIT

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. Further, we first define a hierarchy of XML nodes that we wish to see in our output by specifying the 'TAG' and 'PARENT' of each node. This information about the XML hierarchy we define is stored in a 'UNIVERSAL TABLE' is used for formatting the output. Since the first/top tag in the XML hierarchy has no parent, each SELECT statement using the EXPLICIT mode of the FOR XML statement starts with SELECT 1 AS [TAG], NULL AS [PARENT]

A UNIVERSAL TABLE

• Note that ‘Tag’ and ‘Parent’ metadata columns and also the first row specifies ‘Tag’ value 1 and ‘Parent’ value NULL. Therefore, the corresponding element, <Customer > element, is added as a top/root element in the XML.
                    <Customer cid="C1" name="Janine">

• The second row identifies ‘Tag’ value 2 and ‘Parent’ value 1. Therefore, the element, <Order > element, is added as a child of the <Customer> element.
                   <Customer cid="C1" name="Janine" >
                             <Order id="O1" date=" 1/20/1996">

• The next two rows identify ‘Tag’ value 3 and ‘Parent’ value 2. Therefore, the two elements, <OrderDetail> elements, are added as children of the <Order> element.
                   <Customer cid="C1" name="Janine" >
                             <Order id="O1" date=" 1/20/1996">
                                         <OrderDetail id="OD1" pid=" P1">
                                         <OrderDetail id="OD2" pid=" P2">

• The last row identifies 2 as the ‘Tag’ number and 1 as the ‘Parent’ tag number. Therefore, another <Order> element child is added to the <Customer> parent element.
                   <Customer cid="C1" name="Janine" >
                             <Order id="O1" date=" 1/20/1996">
                                         <OrderDetail id="OD1" pid=" P1">
                                         <OrderDetail id="OD2" pid=" P2">
                             <Order>
                                                 . . .
                             <Order id="O2" date=" 3/29/1996">
                   <Customer>

Specifying Column Names

General format: ElementName ! TagNumber ! AttributeName ! Directive

ElementName: This is the resulting generic identifier of the element.

TagNumber: This is a unique tag value assigned to an element. This value, with the help of the two metadata columns, Tag and Parent, determines the nesting of the elements in the resulting XML.

AttributeName: Provide the name of the attribute to construct in the specified Element Name.

Directive: Directive is optional and you can use it to provide additional information for construction of the XML. Directive has two purposes.
                    1. Encode values as ID, IDREF, and IDREFS.
                    2. Indicate how to map the string data to XML such as ‘hide’, ’element’, ‘xmltext’ etc.

SELECT
    1 AS [Tag],
    NULL AS [Parent],
    [Customers].CustomerID AS [Customer!1!CustomerID],
    ContactName AS [Customer!1!CustomerName],
    CompanyName AS [Customer!1!CompanyName],
    NULL AS [Orders!2!OrderID],
    NULL AS [Orders!2!OrderDate],
    NULL AS [OrderDetails!3!ProductID!ELEMENT],
    NULL AS [OrderDetails!3!UnitPrice!ELEMENT],
    NULL AS [OrderDetails!3!Quantity!ELEMENT],
    NULL AS [OrderDetails!3!Discount!ELEMENT]
FROM
    [Customers]
WHERE
    [Customers].CustomerID = N'ALFKI'

UNION ALL

SELECT
    2 AS [Tag],
    1 AS [Parent],
    [Customers].CustomerID AS [Customer!1!CustomerID],
    ContactName AS [Customer!1!CustomerName],
    CompanyName AS [Customer!1!CompanyName],
    [Orders].OrderID AS [Orders!2!OrderID],
    OrderDate AS [Orders!2!OrderDate],
    NULL AS [OrderDetails!3!ProductID!ELEMENT],
    NULL AS [OrderDetails!3!UnitPrice!ELEMENT],
    NULL AS [OrderDetails!3!Quantity!ELEMENT],
    NULL AS [OrderDetails!3!Discount!ELEMENT]
FROM
    [Customers], [Orders]
WHERE
    [Customers].CustomerID = [Orders].CustomerID
    AND [Customers].CustomerID = N'ALFKI'

UNION ALL

SELECT
    3 AS [Tag],
    2 as [Parent],
    [Customers].CustomerID AS [Customer!1!CustomerID],
    ContactName AS [Customer!1!CustomerName],
    CompanyName AS [Customer!1!CompanyName],
    [Orders].OrderID AS [Orders!2!OrderID],
    OrderDate AS [Orders!2!OrderDate],
    [OrderDetails].ProductID AS [OrderDetails!3!ProductID!ELEMENT],
    UnitPrice AS [OrderDetails!3!UnitPrice!ELEMENT],
    Quantity AS [OrderDetails!3!Quantity!ELEMENT],
    Discount AS [OrderDetails!3!Discount!ELEMENT]
FROM
    [Customers], [Orders],[Order Details] [OrderDetails]
WHERE
    [Customers]. CustomerID = [Orders].CustomerID
    AND [OrderDetails].OrderID = [Orders].OrderID
    AND ([Customers].CustomerID = N'ALFKI')
ORDER BY
    [Customer!1!CustomerID],
    [Orders!2!OrderID],
    [OrderDetails!3!ProductID!ELEMENT]

FOR XML EXPLICIT

 

Clarification

• [OrderDetails!3!ProductID!ELEMENT] is a example for the General format:  ElementName ! TagNumber ! AttributeName ! Directive and ELEMENT is one Directive can be used.
• ORDER BY must be full column names.
• Each query participating in this statement must be valid, and must contain the same number of columns.

• PATH

This is a feature new to SQL Server 2005 and provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a row set, but the PATH mode provides a simpler substitute to the potentially cumbersome EXPLICIT mode queries. In PATH mode, column names or column aliases are treated as XPath expressions, in other words The PATH mode allows you to use an XPath-like syntax as a column name. And also you can use namespaces too.

E.g. 1:

SELECT 2+2 FOR XML PATH

Result (Columns without names):

<row>4</row> 

E.g. 2:

SELECT
    [Products].[ProductID] AS '@ID',
    [ProductName], [UnitPrice]
FROM
    Products
WHERE
    [ProductID]=1
FOR XML PATH 

Result (Column names start with an At Sign (@):

<row ID="1">
    <ProductName>Chai</ProductName>
    <UnitPrice>18.0000</UnitPrice>
</row> 

E.g. 3:

SELECT
    [Employees].[EmployeeID] "@EmpID",
    [TitleOfCourtesy] "Title",
    [FirstName] "Name/First",
    [LastName] "Name/Last"
FROM
    [Employees]
WHERE
    [EmployeeID]=1
FOR XML PATH, ELEMENTS XSINIL 

Result (using XPath and XSINIL (Namespace):

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" EmpID="1">
    <Title>Ms.</Title>
    <Name>
        <First>Nancy</First>
        <Last>Davolio</Last>
    </Name>
</row> 

E.g. 4:

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-sql' as sql)
    SELECT 'SELECT * FROM Customers FOR XML AUTO, ROOT("a")' AS "sql:query"
    FOR XML PATH('sql:root') 

Result (using Namespace):

<sql:root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>SELECT * FROM Customers FOR XML AUTO, ROOT("a")</sql:query>
</sql:root> 

AUTO and RAW modes are easy to use.

Element-Centric XML

Someone wants to create a XML document with full XML Elements (Tags). It is very easy to using 'ELEMENTS' keyword in RAW, AUTO and PATH modes. But you cannot use ELEMENTS keyword in EXPLICIT mode. It’s easy to use. Only have to add ELEMENTS to end of your query.

Result:

References:

• SQL Server Books Online 2005/2000

• .NET e-Business Architecture (Original ISBN: 0-672-32219-6)

[ RATE this ARTICLE, and COMMENT on it ]      [ ABOUT THE AUTHOR ]