Using RAW Mode

RAW mode is probably the easiest of the FOR XML modes to understand. Queries executed using RAW mode simply return an XML element for each row in the resulting rowset. The element contains an attribute for each column retrieved. The elements returned are simply given the generic name row, while each attribute of a row element takes the name of the corresponding column.

To generate an invoice, for example, the developers of the Northwind Traders’ e-commerce solution need to extract a list of items in a particular order as XML. The following FOR XML query could be used:

 SELECT OrderID, ProductID, UnitPrice, Quantity FROM [Order Details] WHERE OrderID = 10248 FOR XML RAW 

This query would produce the following XML fragment:

 <row Order Product UnitPrice="14" Quantity="12"/> <row Order Product UnitPrice="9.8" Quantity="10"/> <row Order Product UnitPrice="34.8" Quantity="5"/> 

You can execute this query by running RAW.vbs in the Demos\Chapter2 folder on the companion CD.

Using Joins in RAW Mode Queries

Note that since each row in a RAW mode result set is represented by a single element, all elements in the fragment are empty—that is, they contain no values or subelements. All data is contained in attributes. As I mentioned, mapping columns in a table to attributes in an XML document is referred to as attribute-centric mapping. RAW mode queries always return attribute-centric XML, including queries containing a join. For example, to generate an invoice containing order data such as the order date as well as the list of items ordered, the query would need to retrieve data from both the Orders and Order Details tables, as shown in the following example:

 SELECT Orders.OrderID, OrderDate, ProductID, UnitPrice, Quantity FROM Orders JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE Orders.OrderID = 10248 FOR XML RAW 

This query returns the following XML fragment:

 <row Order OrderDate="1996-07-04T00:00:00" Product      UnitPrice="14" Quantity="12"/> <row Order OrderDate="1996-07-04T00:00:00" Product     UnitPrice="9.8" Quantity="10"/> <row Order OrderDate="1996-07-04T00:00:00" Product      UnitPrice="34.8" Quantity="5"/> 

Using Column Aliases to Specify Attribute Names

Column aliases can be used to change the names of the attributes returned or to provide a name for a calculated column. However, in a RAW mode query, there’s no way to change the name of the elements; you must always use the generic row. The following example shows how to use an alias to specify the names of the attributes returned:

 SELECT OrderID InvoiceNo,         SUM(Quantity) TotalItems FROM [Order Details] WHERE OrderID = 10248 GROUP BY OrderID FOR XML RAW 

This query returns the following XML fragment:

 <row InvoiceNo="10248" TotalItems="27"/> 

You can execute this query by running RAWGroupBy.vbs in the Demos\Chapter2 folder on the companion CD.