Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Using Subqueries with FOR XML AUTO

The AUTO mode of the FOR XML extension lets you use subqueries, which can be a useful trick for certain XML formatting needs. Subqueries can be used for a variety of reasons, one of them being limiting the returned result set. The following example returns XML with a couple of customers who use United States as their shipping country:

SELECT TOP 2 CompanyName, City, Region FROM customers WHERE CustomerID IN 
(SELECT customerID FROM orders
WHERE ShipCountry = 'USA')
FOR XML AUTO

Results:

<customers CompanyName="Great Lakes Food Market" City="Eugene" Region="OR"/>
<customers CompanyName="Hungry Coyote Import Store" City="Elgin" Region="OR"/>

Similarly, the next example retrieves top title names for a couple of authors in the pubs database; If there aren't any titles written by a particular author, the query will return no titles for this author:

SELECT au_lname, au_fname, title =
ISNULL(
(SELECT TOP 1 title FROM titles 
INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id
WHERE titleauthor.au_id = authors.au_id), 'no titles for this author')
FROM authors
WHERE au_lname = 'bennet'
OR au_lname = 'blotchet-halls'
FOR XML AUTO

Results:

<authors au_lname="Bennet" 
au_fname="Abraham" 
title="The Busy Executive&apos;s Database Guide"/>

<authors au_lname="Blotchet-Halls" 
au_fname="Reginald" 
title="Fifty Years in Buckingham Palace Kitchens"/>

The advantage of using a subquery in this case is the ability to suppress the child tag. The same query could be rewritten with joins, however. In that case, you might have to show <titles> tags along with the <author>, as shown following:

SELECT au_lname, au_fname, title 
FROM authors 
INNER JOIN titleauthor 
ON authors.au_id = titleauthor.au_id
LEFT JOIN titles 
ON titles.title_id = titleauthor.title_id
WHERE au_lname = 'bennet'
OR au_lname = 'blotchet-halls'
FOR XML AUTO

Results:

<authors au_lname="Bennet" 
au_fname="Abraham">
	<titles title="The Busy Executive&apos;s Database Guide"/>
</authors>
<authors au_lname="Blotchet-Halls" 
au_fname="Reginald">
	<titles title="Fifty Years in Buckingham Palace Kitchens"/>
</authors>

A similar query can be written using a join and aliasing the inner table to suppress the child tag:

SELECT au_lname, au_fname, 
SUBSTRING(title, 1, LEN(title)) as title
FROM authors 
INNER JOIN titleauthor 
ON authors.au_id = titleauthor.au_id
LEFT JOIN titles ON titles.title_id = titleauthor.title_id
WHERE au_lname = 'bennet'
OR au_lname = 'blotchet-halls'
FOR XML AUTO

Results:

<authors au_lname="Bennet" 
au_fname="Abraham" 
title="The Busy Executive&apos;s Database Guide"/>

<authors au_lname="Blotchet-Halls" 
au_fname="Reginald" 
title="Fifty Years in Buckingham Palace Kitchens"/>

As you can tell, the AUTO mode is very easy to use after you know how it works. On the other hand, the AUTO mode doesn't give you much flexibility in formatting the result set. In addition, the AUTO mode does not support GROUP BY clause and aggregate functions.

You can get somewhat creative if you still want to use FOR XML AUTO with aggregate functions, for instance the above query could be rewritten to return the average quantity using FOR XML AUTO as follows:

SELECT TOP 1 average_quantity = (
SELECT AVG(quantity) FROM [order details])
FROM [order details]
FOR XML AUTO

Results:

<order_x0020_details average_quantity="23"/>

Nor can you retrieve a scalar variable into XML using FOR XML AUTO (unless you put such a variable in a temporary table). You'll see how to overcome these limitations of the AUTO mode with RAW mode later in the article.

  • + Share This
  • 🔖 Save To Your Account