How to Retrieve Dynamic XML Data from T-SQL

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:

1. RAW
2. AUTO
3. 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.