SELECTFOR XML

for RuBoard

SELECT FOR XML

As the examples in the previous chapter demonstrated, you can retrieve XML data from SQL Server using the FOR XML option of the SELECT command. FOR XML causes SELECT to return query results as an XML stream rather than a traditional rowset. This stream can have one of three formats: RAW, AUTO, or EXPLICIT. The basic FOR XML syntax looks like this:

 SELECT column list FROM table list WHERE filter criteria FOR XML RAW  AUTO  EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] 

Although I'll discuss these options separately, let's go over them briefly now.

RAW returns column values as attributes and wraps each row in a generic row element. AUTO returns column values as attributes and wraps each row in an element named after the table from which it came. (There's actually more to this than simply naming each row after the table, view, or UDF that produced it. SQL Server uses a set of heuristics to decide what the actual element names are with FOR XML AUTO.) EXPLICIT lets you completely control the format of the XML returned by a query.

XMLDATA causes an XDR schema to be returned for the document being retrieved. ELEMENTS causes the columns in XML AUTO data to be returned as elements rather than attributes. BINARY BASE64 specifies that binary data is to be returned using BASE64 encoding.

for RuBoard