SQLUNINTERRUPTED

Continuing with the series on XML, today we will talk about the FOR XML EXPLICIT clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML EXPICIT clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs/Northwind database, while SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

Using the RAW and AUTO mode, we did not have any control over the Nesting of Elements in the XML result. The EXPLICIT mode, provides us more control over the nesting of the elements, but doing so requires a careful understanding of what is the desired nesting level and how to implement the same in your Query.

To use the EXPLICIT mode, we need to write the query in a way to produce the universal table. This table is nothing but a relational structure defining the Nesting of the Elements. Proper order by clauses have to be used to make sure that a Parent and all its children and their children are all together in the universal table.

To accomplish this, we have to always define the 1st and 2nd column in our select query as the TAG and PARENT column. The TAG column will indicate the Nest Level for each element in the XML, while the Parent column would be used to represent the Parent/Child relationship.

An example of a universal table is presented below.

When SQL Server tries to convert this output into XML, it will pick all ELEMENTS with TAG value of 1 and Parent value of 0, and make them Root Elements. Similarly, elements with TAG value 2 and Parent Value 1, and occurring immediately after the Root Element will be made a child of the Root Element, and so on.

Once again the order of data in the universal table will define the nesting. For Example consider the query below, where I have ordered the result using TAG column.

SELECT 1                    as Tag,
         NULL                 as Parent,
         Customers.CustomerID as [Customer!1!CustomerID],
         NULL                 as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
         1,
         Customers.CustomerID,
         Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY TAG

<<<<<<<<<<<<<<<<<<<<<< Universal Table Snippet >>>>>>>>>>>>>>>

In this case, SQL will create an XML with multiple root nodes, with the Last root Node having multiple child!

Also note the naming mechanism of the columns used with EXPLICIT mode. The Columns have to named as

ElementName!TagNumber!AttributeName!directive

As with RAW and AUTO mode, EXPLICIT mode can also be used in conjunction some directives. SQL 2000 directives are listed below.

ID : An element attribute can be specified to be an ID type attribute. IDREF and IDREFS attributes can then be used to refer to them, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

IDREF : Attributes specified as IDREF can be used to refer to ID type attributes, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

IDREFS: Attributes specified as IDREFS can be used to refer to ID type attributes, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

HIDE: The attribute is not displayed. This may be useful for ordering the result by an attribute that will not appear in the result.

ELEMENT: This does not generate an attribute. Instead it generates a contained element with the specified name (or generate contained element directly if no attribute name is specified). The contained data is encoded as an entity (for example, the < character becomes &lt;). This keyword can be combined with ID, IDREF, or IDREFS.

XML: This is the same as an element directive except that no entity encoding takes place (for example, the < character remains <). This directive is not allowed with any other directive except hide.

XMLTEXT: The column content should be wrapped in a single tag that will be integrated with the rest of the document. This directive is useful in fetching overflow (unconsumed) XML data stored in a column by OPENXML. If AttributeName is specified, the tag name is replaced by the specified name; otherwise, the attribute is appended to the current list of attributes of the enclosing elements and by putting the content at the beginning of the containment without entity encoding. The column with this directive must be a text type (varchar, nvarchar, char, nchar, text, ntext). This directive can be used only with hide. This directive is useful in fetching overflow data stored in a column. If the content is not a well-formed XML, the behavior is undefined.

CDATA: Contains the data by wrapping it with a CDATA section. The content is not entity encoded. The original data type must be a text type (varchar, nvarchar, text, ntext). This directive can be used only with hide. When this directive is used, AttributeName must not be specified.

I will be using some of these directives in a single query to display the results.

SELECT 1                      as Tag,
         NULL                 as Parent,
         Customers.CustomerID as [Customer!1!CustomerID!ID],
     NULL               as [CustomerName!2!Name!IDREF],
     NULL                  as [CustomerName!2!Phone!Hide],
         NULL                 as [Order!3!OrderID],
     NULL              as [Order!3!OrderDate!ELEMENT]
FROM Customers
UNION ALL
SELECT 2,
         1,
         Customers.CustomerID,
     Customers.ContactName,
     Customers.Phone,   
         NULL,
     NULL
FROM Customers
UNION ALL
SELECT 3,
    2,
    Customers.CustomerID,
    Customers.ContactName,
    NULL,
    Orders.OrderID,
    Orders.OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID!ID],[CustomerName!2!Name!IDREF], [Order!3!OrderID]
FOR XML EXPLICIT, XMLDATA

<<<<<<<<<<<<<<<<<< Snippet Output >>>>>>>>>>>>>>>>>

<Schema name="Schema5" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Customer" content="mixed" model="open">
        <AttributeType name="CustomerID" dt:type="id"/>
        <attribute type="CustomerID"/>
</ElementType>
<ElementType name="CustomerName" content="mixed" model="open">
    <AttributeType name="Name" dt:type="idref"/><attribute type="Name"/>
</ElementType>
<ElementType name="Order" content="mixed" model="open">
    <AttributeType name="OrderID" dt:type="i4"/><attribute type="OrderID"/>
<Element type="OrderDate"/></ElementType>
    <ElementType name="OrderDate" content="textOnly" model="closed" dt:type="dateTime"/>
    </ElementType>
</ElementType>
</Schema>
<Customer xmlns="x-schema:#Schema5" CustomerID="ALFKI">
    <CustomerName Name="Maria Anders">
        <Order OrderID="10643">
            <OrderDate>1997-08-25T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10692">
            <OrderDate>1997-10-03T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10702">
            <OrderDate>1997-10-13T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10835">
            <OrderDate>1998-01-15T00:00:00</OrderDate>
        </Order>

<<<<<<<<<<<<<<<<<< Snippet Output>>>>>>>>>>>>>>>>>>>>

The same directives and constructs can be used with SQL 2005 and SQL 2008.

Continuing with the series on XML, today we will talk about the FOR XML AUTO clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML AUTO clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs database, while those for SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

FOR XML AUTO Mode

When we use the AUTO mode, we get an XML representation of each row in the T-SQL result set.  When using AUTO mode, SQL server tries to create a nested XML output, in case of multi-table joins. For every table, which has at lease one column from the table in the select list an element is added, correspondingly, the column for that table become the element attributes.

The hierarchy (nesting of the elements) in the result set is based on the order of tables identified by the columns specified in the SELECT clause; therefore, the order in which column names are specified in the SELECT clause is significant. The tables are identified and nested in the order in which the column names are listed in the SELECT clause. The first, leftmost table identified forms the top element in the resulting XML document. The second leftmost table (identified by columns in the SELECT statement) forms a sub-element within the top element, and so on.

The basic syntax of the FOR XML AUTO mode, is similar to the RAW mode.

SELECT <col1, col2,….>
From <table>
FOR XML AUTO, <directives,..>

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO

<<<<<<<< Snippet output >>>>>>>>

<Customers CustomerID="ALFKI" ContactName="Maria Anders">
  <Orders OrderID="10643"/>
  <Orders OrderID="10692"/>
  <Orders OrderID="10702"/>
  <Orders OrderID="10835"/>
  <Orders OrderID="10952"/>
  <Orders OrderID="11011"/>
</Customers>

<<<<<<<< Snippet output >>>>>>>>

When the Element, directive is supplied, the column values are converted to Child Elements for the Table Element as mentioned.

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, ELEMENTS

<Customers>
    <CustomerID>ALFKI</CustomerID>
    <ContactName>Maria Anders</ContactName>
    <Orders>
        <OrderID>10643</OrderID>
    </Orders>
    <Orders>
        <OrderID>10692</OrderID>
    </Orders>
    …
</Customer>

With SQL 2005 similar enhancements were made to the XML AUTO mode. We can use the other directives with AUTO mode, like we do for RAW mode as described in my post.

Starting with SQL Server 2000 we can query Relational Tables to produce XML output. The XML produced by SQL in this case is not a well formed XML. Its basically an XML like representation of the Result set with opening and closing tags and attribute values for each element.

In this article, I will talk about the FOR XML RAW clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs database, while those for SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

FOR XML RAW Mode

When we use the RAW mode, we get an XML representation of each row in the T-SQL result set. The order in which the values are returned is exactly the same as that specified in the Select List. If you look closely at the output of the query, you would notice that there is a basic row element for each row in the output, with the column values forming the attributes.

The basic syntax for RAW mode queries is

SELECT <col1, col2,….>
From <table>
FOR XML RAW, <directives,..>

SELECT TOP 10 emp_id, FName, LName,  Hire_Date
FROM Employee 
FOR XML RAW

<<<< Snippet output >>>>>>

<row emp_id="PMA42628M" FName="Paolo" LName="Accorti" Hire_Date="1992-08-27T00:00:00"/>
<row emp_id="PSA89086M" FName="Pedro" LName="Afonso" Hire_Date="1990-12-24T00:00:00"/>

<<<< Snippet output >>>>>>

The following directives can be used in the RAW mode, when working with SQL 2000.

1. XMLDATA: Includes the XML Schema that describes the data.
2. BINARY BASE64: If we have an Image column in the table, it will be converted to BASE64 encoding in the XML output.

In SQL 2005, some modifications were made to the RAW mode. Some of the improvements which were added were

1. Ability to rename the root node.
2. ELEMENTS directive can also be used with RAW mode.
3. XMLSCHEMA directive
4. XSINIL directive
5. TYPE directive

With the ELEMENTS directive, all attribute values can be converted into ELEMENT representation as shown below.

SELECT TOP 10 EmployeeID,
FirstName, LastName, EmailAddress, HireDate
FROM humanresources.Employee Employee JOIN person.contact Contact
        ON Employee.ContactID = Contact.ContactID
FOR XML RAW, ELEMENTS

<<<< Snippet output >>>>>>

<row>
  <EmployeeID>1</EmployeeID>
  <FirstName>Guy</FirstName>
  <LastName>Gilbert</LastName>
  <EmailAddress>guy1@adventure-works.com</EmailAddress>
  <HireDate>1996-07-31T00:00:00</HireDate>
</row>

<<<< Snippet output >>>>>>

The row root node can now be renamed to any name of our choice as shown below.

SELECT TOP 10 EmployeeID,
FirstName, LastName, EmailAddress, HireDate
FROM humanresources.Employee Employee JOIN person.contact Contact
        ON Employee.ContactID = Contact.ContactID
FOR XML RAW (‘EMPLOYEE’), ELEMENTS

<<<< Snippet output >>>>>>

<EMPLOYEE>
  <EmployeeID>1</EmployeeID>
  <FirstName>Guy</FirstName>
  <LastName>Gilbert</LastName>
  <EmailAddress>guy1@adventure-works.com</EmailAddress>
  <HireDate>1996-07-31T00:00:00</HireDate>
</EMPLOYEE>

<<<< Snippet output >>>>>>

The other directives and their functionality are mentioned below.

1. XMLSCHEMA: Provides the SCHEMA definitions for the XML output.
2. XSINIL: Would also return a value for NULL elements, indicting that the xsi:nil property is true for these elements.
3. TYPE: Will not change the output in anyway, will just return XML type output. Which can be further assigned to an XML type variable.