Introduction
In this article we will see the different ways of retrieving XML data from SQL
Server.
Classifications of XML Output
SQL Server provides an XML option to use with the FOR
clause, allowing for an easy method of converting table data into XML nodes.
FOR XML can take different arguments – let’s find out which one works for us.
The command allows three types:
- RAW
- AUTO
- EXPLICIT
Let’s see an example to understand the details.
1. RAW Mode
The RAW mode generates a single <row>
element per row in the row set that is returned by the
SELECT
statement. You can generate an XML hierarchy by writing nested
FOR XML
queries.
Query
SELECT '<rows>'+
(
SELECT E.Empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
E.sal,
E.comm,
E.dept,
D.dname,
d.loc
FROM
Employee E
INNER JOIN
Department D
ON
E.Empno = D.Empno
FOR XML RAW
)+'</rows>'
Output
Here is the output generated by the RAW mode.
<rows>
<row Empno="1" ename="JOHNSON" job="ADMIN" mgr="6" hiredate="1990-12-17T00:00:00"
sal="18000.00" dept="4" dname="IT" loc="INDIA"/>
<row Empno="2" ename="HARDING" job="MANAGER" mgr="9" hiredate="1998-02-02T00:00:00"
sal="52000.00" comm="300.00" dept="3" dname="HR" loc="UK"/>
<row Empno="3" ename="TAFT" job="SALES I" mgr="2" hiredate="1996-01-02T00:00:00"
sal="25000.00" comm="500.00" dept="3" dname="Finance" loc="USA"/>
<row Empno="4" ename="HOOVER" job="SALES I" mgr="2"
hiredate="1990-04-02T00:00:00" sal="27000.00" dept="3" dname="Purchase" loc="Singapore"/>
</rows>
The XML document produced contains an element <Row>
, which is fixed, for each record of the result set generated by
the query. This is not very useful
because we have no control over the element naming and document structure.
Query
SELECT '<rows>'+
(
SELECT E.Empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
E.sal,
E.comm,
E.dept,
D.dname,
d.loc
FROM
Employee E
INNER JOIN
Department D
ON
E.Empno = D.Empno
FOR XML RAW ('Employee'), ELEMENTS
)+'</rows>'
Output
<rows>
<Employee>
<Empno>1</Empno><ename>JOHNSON</ename><job>ADMIN</job><mgr>6</mgr>
<hiredate>1990-12-17T00:00:00</hiredate><sal>18000.00</sal>
<dept>4</dept><dname>IT</dname><loc>INDIA</loc>
</Employee>
<Employee>
<Empno>2</Empno><ename>HARDING</ename><job>MANAGER</job><mgr>9
</mgr><hiredate>1998-02-02T00:00:00</hiredate><sal>52000.00</sal>
<comm>300.00</comm><dept>3</dept><dname>HR</dname><loc>UK</loc>
</Employee>
<Employee>
<Empno>3</Empno><ename>TAFT</ename><job>SALES I</job><mgr>2</mgr>
<hiredate>1996-01-02T00:00:00</hiredate><sal>25000.00</sal>
<comm>500.00</comm><dept>3</dept><dname>Finance</dname><loc>USA</loc>
</Employee>
<Employee>
<Empno>4</Empno><ename>HOOVER</ename><job>SALES I</job>
<mgr>2</mgr><hiredate>1990-04-02T00:00:00</hiredate><sal>
27000.00</sal><dept>3</dept><dname>Purchase</dname><loc>Singapore</loc>
</Employee>
</rows>
The above query generates an XML with nodes for item ('Employee
') in data.
2. AUTO Mode
The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the
SELECT
statement is specified. You have
minimal control over the shape of the XML generated.
Query
SELECT '<rows>'+
(
SELECT E.Empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
E.sal,
E.comm,
E.dept,
D.dname,
d.loc
FROM
Employee E
INNER JOIN
Department D
ON
E.Empno = D.Empno
FOR XML AUTO
)+'</rows>'
Output
Below is output structure generated by the AUTO mode.
<rows>
<E Empno="1" ename="JOHNSON" job="ADMIN" mgr="6"
hiredate="1990-12-17T00:00:00" sal="18000.00" dept="4"><D dname="IT" loc="INDIA"/>
</E>
<E Empno="2" ename="HARDING" job="MANAGER" mgr="9"
hiredate="1998-02-02T00:00:00" sal="52000.00" comm="300.00" dept="3"><D dname="HR" loc="UK"/>
</E>
<E Empno="3" ename="TAFT" job="SALES I" mgr="2"
hiredate="1996-01-02T00:00:00" sal="25000.00" comm="500.00" dept="3"><D dname="Finance" loc="USA"/>
</E>
<E Empno="4" ename="HOOVER" job="SALES I" mgr="2"
hiredate="1990-04-02T00:00:00" sal="27000.00" dept="3"><D dname="Purchase" loc="Singapore"/>
</E>
</rows>
As you can see the <Employee>
and <Department>
tags have a parent-child relationship, giving us the hierarchical structure we
require. This node relationship is determined on the order in which the tables are declared within the query, as explained above.
3. Explicit Mode
The EXPLICIT mode is implemented through UNION ALL
queries. If you're not familiar with the
UNION ALL
clause of the SELECT
statement, it simply combines the results of two or more queries.
Each query combined, with the UNION ALL
clause, has to contain the same number of columns. The corresponding columns in each query
need to have compatible data types. In other words, you cannot UNION
an integer and a string (unless you explicitly convert one of them first).
For instance, I could combine the names of customer contacts and employee names with the following query:
SELECT ContactName
FROM
Customers
UNION ALL
SELECT FirstName + ' ' + LastName As FullName
FROM
Employees
Output
ContactName
-------------------------
James J
Antonio Moreno
Thomas Hardy
Christina Berglund
Hanna Moos
Notice that even though I have combined two queries with different column names, the output has a column ContactName. The EXPLICIT mode works similarly; you define your XML hierarchy in the top query and then you take data for each of the XML nodes from the queries that follow.
(Note: Each query will have to contain the same number of columns with compatible data types.)
The following query results in titles written by the author Marc and respective royalty percentage.
SELECT 1 As TAG,
NULL As PARENT,
authors.au_fname As authors!1!au_fname,
authors.au_lname As authors!1!au_lname,
NULL As titleauthor!2!royaltyper,
NULL As titles!3!title
FROM
Authors
WHERE
au_lname = 'Marc'
UNION ALL
SELECT 2 As TAG,
1 As PARENT,
au_fname,
au_lname,
royaltyper,
NULL
FROM
Authors
INNER JOIN
titleauthor
ON
authors.au_id= titleauthor.au_id
WHERE
au_lname ='Marc'
UNION ALL
SELECT 3 As TAG,
2 As PARENT,
au_fname,
au_lname,
royaltyper,
title
FROM
authors
INNER JOIN
titleauthor
ON
authors.au_id = titleauthor.au_id
INNER JOIN
titles
ON
titles.title_id = titleauthor.title_id
WHERE
au_lname ='Mac'
ORDER BY
authors!1!au_fname,
authors!1!au_lname,
titleauthor!2!royaltyper
FOR XML EXPLICIT
Output
<authors au_fname="James J" au_lname="Marc">
<titleauthor royaltyper="80">
<titles title="Introduction to SQL Server 2008"/>
</titleauthor>
<titleauthor royaltyper="75">
<titles title="T-SQL: A Beginners Guide"/>
</titleauthor>
</authors>
Hurray!!! I hope you guys have fun playing with XML and T-SQL.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.