Introduction to Formatting Query Results as XML in Microsoft SQL Server 2008

We can use the FOR XML clause with SELECT statements in Microsoft SQL Server to produce XML output from our queries.  We might use this feature when creating output that will be consumed by an application that understands XML.  This post will demonstrate using this feature to create XML output of a contact list.


Here is the T-SQL used to create and populate our Contacts table:

CREATE TABLE Contacts
(
ContactId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL
);

INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Smith', 'Jon', '254-555-1234', 'jon@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Rogers', 'Amanda', '254-555-4321', 'amanda@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Gomez', 'Paul', '254-555-5252', 'paul@example.com' );


INSERT INTO Contacts ( FirstName, LastName, PhoneNumber, EmailAddress )
VALUES ( 'Peterson', 'James', '254-555-9876 ext 234', 'james@example.com' );

Let us assume that we have a contact management application that we wish to export this data to and that this fictitious application can import XML that is formatted like the sample below.


<?xml version="1.0"?>
<Contact-List>
<Contact>
<First-Name>John</First-Name>
<Last-Name>Doe</Last-Name>
<Telephone>800-555-1111</Telephone>
<E-mail>doe.john@example.com</E-mail>
</Contact>
<Contact>
<First-Name>Tommy</First-Name>
<Last-Name>Atkins</Last-Name>
<Telephone>800-555-2222</Telephone>
<E-mail>atkins.tommy@example.com</E-mail>
</Contact>
</Contact-List>


As you can see in addition to formatting the output as XML we will also need to map our table's columns to the XML format's elements: "FirstName" in the table will be "First-Name" in the XML.   We can use the FOR XML clause in our SELECT statement to shape the query's result to match the required format.  Below is the T-SQL used to create the XML.

SELECT 

FirstName AS 'First-Name',

LastName AS 'Last-Name',

PhoneNumber AS Telephone,

EmailAddress AS 'E-mail'

FROM Contacts

ORDER BY LastName, FirstName

FOR XML PATH('Contact'), ROOT('Contact-List');

Here is the result of that query.

<Contact-List>

  <Contact>

    <First-Name>Rogers</First-Name>

    <Last-Name>Amanda</Last-Name>

    <Telephone>254-555-4321</Telephone>

    <E-mail>amanda@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Peterson</First-Name>

    <Last-Name>James</Last-Name>

    <Telephone>254-555-9876 ext 234</Telephone>

    <E-mail>james@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Smith</First-Name>

    <Last-Name>Jon</Last-Name>

    <Telephone>254-555-1234</Telephone>

    <E-mail>jon@example.com</E-mail>

  </Contact>

  <Contact>

    <First-Name>Gomez</First-Name>

    <Last-Name>Paul</Last-Name>

    <Telephone>254-555-5252</Telephone>

    <E-mail>paul@example.com</E-mail>

  </Contact>

</Contact-List>

The FOR XML clause instructs the server to transform the results to XML.  The PATH parameter specifies that each record should be wrapped in an element named "Contact".  The ROOT parameter specifies that the result set should be wrapped in an element names "Contact-List".  Note that since the elements contain dashes and dashes are not permitted in column names an alias is needed in our query to name the elements to match the XML elements.   Note that element names in query such as 'First-Name' can be escaped by single quotes, square brackets, or double quotes.  All of the following are equivlement: 'First-Name', "First-Name", [First-Name].

The FOR XML has additional parameters that can be used to control the format of the output.  This post is intended as a basic introduction.

References

"Basic Syntax of the FOR XML Clause" from SQL Server Books Online

http://msdn.microsoft.com/en-us/library/ms190922.aspx