Professional SQL Server™ 2005 Programming

16.2. Retrieving Relational Data in XML Format

This is an area that SQL Server already had largely figured out prior to the 2005 release. We had couple of different options, and we had still more options within those options — between them all, things have been pretty flexible for quite some time. Let's take a look.

16.2.1. The FOR XML Clause

This clause is at the root of most of the different integration models available. With the exception of XML mapping schemas (fairly advanced, but we'll touch on them briefly later in the chapter) and the use of XPath, FOR XML will serve as the way of telling SQL Server that it's XML that you want back, not the more typical result set. It is essentially just an option added onto the end of the existing T-SQL SELECT statement.

Let's look back at the SELECT statement syntax from Chapter 3:

SELECT <column list>
[FROM <source table(s)>]
[WHERE <restrictive condition>]
[GROUP BY <column name or expression using a column in the SELECT list>
[HAVING <restrictive condition based on the GROUP BY results>]
[ORDER BY <column list>]
[FOR XML {RAW|AUTO|EXPLICIT|PATH}
    [, XMLDATA][, ELEMENTS][, BINARY base64]]
[OPTION (<query hint>, [, ...n])]

Most of this should seem pretty trivial by now — after all, we've been using this syntax throughout a lot of hard chapters by this time — but it's time to focus in on that FOR XML line.

FOR XML provides four different initial options for how you want your XML formatted in the results:

• RAW — This sends each row of ...