SQLUNINTERRUPTED

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.