- XML Support in Transact-SQL
- Transact-SQL XML Extensions
- FOR XML AUTO
- Using Subqueries with FOR XML AUTO
- FOR XML RAW
- Summary
Like this article? We recommend
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'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'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'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.