SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Converting Relational Tables to XML documents using FOR XML RAW

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.
Advertisements

3 responses to “Converting Relational Tables to XML documents using FOR XML RAW

  1. Pingback: Working with XML in Microsoft SQL Server « SQLUninterrupted

  2. Pingback: Converting Relational Tables to XML documents using FOR XML AUTO « SQLUninterrupted

  3. Pingback: Converting Relational Tables to XML documents using FOR XML EXPLICIT « SQLUninterrupted

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: