SQL Server: For XML Clause

The FOR XML clause specifies that the result of the SELECT statement should be returned in XML format. DBA/Developer can specify one of the following modes with the FOR XML clause:

RAW: A single <row> element will be generated for each row in the rowset. Each non-null column value generates an attribute with the name identical to the column's name or the column's alias.

AUTO: Nested elements are returned using the columns specified in the SELECT list. Each non-null column value generates an attribute named according to the column name or column alias. The element nesting is based on the order in which the columns are specified in

the SELECT list.

EXPLICIT: Each row in the rowset can be defined in detail, including attributes and

elements.

PATH: Each row in the rowset can be defined in detail, but column names and column

aliases are specified as XPath expressions.

DBA/Developer can also include the ELEMENTS option with the FOR XML clause. This will return columns in the SELECT list as subelements, rather than as attributes. Each table specified in the FROM clause will be represented by a separate element, and each column from that table will appear as a subelement of that element. Tables specified first will constitute higher-level elements of the hierarchy, and if specified, column aliases will be used as element names. For example, in this scenario, DBA/Developer might use the following query to extract data from the Product, ProductVendor, and Vendor tables in XML format:

SELECT
p.Name AS Product,
v.Name AS Vendor,
p.ProductID

FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing. Vendor v
ON pv.VendorID = v.VendorID
FOR XML RAW,
ELEMENTS;

The query would display the XML in the following format:

 <row> <Product>Product A

</Product><Vendor>VirtuArt, Inc.
</Vendor><ProductID>1

</ProductID></row><row>
<Product>Product B

</Product><Vendor>NuTex Corporation

</Vendor><ProductID>879

</ProductID></row><row>
<Product>Product C
</Product><Vendor>VirtuArt, Inc.
</Vendor><ProductID>712
</ProductID></row><row>
<Product>Product </Product>
<Vendor>InterConn
</Vendor><ProductID>2
</ProductID></row>

DBA/Developer should not implement a query that uses the OPENXML function in the FROM clause, or create a Transact- SQL script that uses the sp_xml_preparedocument system stored procedure and then inserts the XML into an xml data type column. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. This function can be used in SELECT statements where a table or view would be specified to extract data from an XML document. One of the function's required parameters is an integer used as a handle to the internal representation of the XML document. Therefore, before DBA/Developer can extract data from an XML document, DBA/Developer must call the sp_xml_preparedocument stored procedure to create and return the document handle. After the data has been extracted from the XML document by a SELECT query with the OPENXML function, DBA/Developer would call the sp_xml_removedocument stored procedure to remove the internal representation of the document and free resources. DBA/Developer should not create a stored procedure that queries the tables and returns an xml data type. In this scenario, DBA/Developer wanted to display the data in XML format. Therefore, DBA/Developer would still have to display the result of the returned xml data type.