Database Reference
In-Depth Information
Converting Relational Data to XML
A
SELECT
query returns results as a row set. You can optionally retrieve results of a SQL query as XML by
specifying the
FOR
XML clause in the query. SQL Server 2005 enables you to extract relational data into
XML form, by using the
FOR
XML clause in the
SELECT
statement. SQL Server 2005 extends the
FOR
XML
capabilities, making it easier to represent complex hierarchical structures and add new keywords to the
modify the resulting XML structure.
Note
In Chapter 13, I show how to extract data from a data set, convert it to XML, and write it to a file with the
data set's
WriteXml
method.
The
FOR
XML clause converts result sets from a query into an XML structure, and it provides four
modes of formatting:
FOR XML RAW
•
FOR XML AUTO
•
•
FOR XML PATH
•
FOR XML EXPLICIT
We'll use the first two in examples to show how to generate XML with a query.
Using FOR XML RAW
The
RAW
mode transforms each row in the query result set into an XML element identified as
row
for each
row displayed in the result set. Each column name in the
SELECT
statement is added as an attribute to the
row
element while displaying the result set.
By default, each column value in the row set that is not
NULL
is mapped to an attribute of the
row
element.
Try It: Using FOR XML RAW (Attribute-centric)
1.
Let's produce the SQL query result in raw XML format.
FOR XML RAW
is the
statement we will use to produce the output, as you will see in the following
steps. In the Object Explorer, expand the Databases node, select the
AdventureWorks database, and click the New Query window. Enter the
following query, and click Execute:
select Person.Contact.Title, Person.Contact.FirstName, Person.Contact.LastNamefrom
Person.Contact
where Person.Contact.Title ='Mr.'
for xml raw
2.
You will see a link in the results pane of the query pane. Click the link, and you
should see the results shown in Figure 7-1.
