Using AUTO Mode

AUTO mode gives you more control over the XML returned. By default, each row in the result set is represented as an XML element named after the table it was selected from. For example, data could be retrieved from the Orders table using an AUTO mode query, as shown in this example:

 SELECT OrderID, CustomerID FROM Orders WHERE OrderID = 10248 FOR XML AUTO 

This query returns the following XML fragment:

 <Orders Order Customer/> 

In cases in which table names contain spaces, the resulting XML element names contain encoding characters. For example, we could retrieve our invoice data from the Order Details table using the following AUTO mode query:

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

However, the resulting XML fragment would look like this:

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

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

Using Aliases in AUTO Mode Queries

To get around the problem of the resulting XML element names containing encoding characters, we can use aliases. As with RAW mode, column aliases can be used to rename attributes. In AUTO mode queries, however, you can also rename the elements using table aliases, as shown in the following example:

 SELECT OrderID InvoiceNo,         ProductID,         UnitPrice Price,         Quantity FROM [Order Details] Item WHERE OrderID = 10248 FOR XML AUTO 

The XML fragment returned by this query follows. Note that the element name has been returned as Item, which is the alias used in the query.

 <Item InvoiceNo="10248" Product Price="14" Quantity="12"/> <Item InvoiceNo="10248" Product Price="9.8" Quantity="10"/> <Item InvoiceNo="10248" Product Price="34.8" Quantity="5"/> 

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

Joins in AUTO Mode

Queries with joins in AUTO mode behave differently from RAW mode queries containing joins. Each table in the join results in a nested XML element. For example, a query to generate an invoice from the Orders and Order Details tables could be written as an AUTO mode query, as shown here:

 SELECT Invoice.OrderID InvoiceNo,         OrderDate,         ProductID,         UnitPrice Price,         Quantity FROM Orders Invoice JOIN [Order Details] Item ON Invoice.OrderID = Item.OrderID WHERE Invoice.OrderID = 10248 FOR XML AUTO 

When executed in AUTO mode, the XML fragment returned is significantly different from the results of a JOIN query using RAW mode, as shown by this partial result set:

 <Invoice InvoiceNo="10248" OrderDate="1996-07-04T00:00:00">     <Item Product Price="14" Quantity="12"/>     <Item Product Price="9.8" Quantity="10"/>     <Item Product Price="34.8" Quantity="5"/> </Invoice> 

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

Using the ELEMENTS Option

Another difference between the RAW and AUTO modes is that the ELEMENTS option can be used in AUTO mode to produce element-centric XML results. When ELEMENTS is specified in an AUTO mode query, all columns are returned as subelements of the element representing the table they belong to. For example, here’s how the query used to retrieve invoice data would look with the ELEMENTS option specified:

 SELECT Invoice.OrderID InvoiceNo,         OrderDate,         ProductID,         UnitPrice Price,         Quantity FROM Orders Invoice JOIN [Order Details] Item ON Invoice.OrderID = Item.OrderID WHERE Invoice.OrderID = 10248 FOR XML AUTO, ELEMENTS 

The resulting XML fragment contains an Invoice element with a subelement for each column. The Invoice element contains an Item element, which also has a subelement for each column, as shown in this partial result set:

 <Invoice>     <InvoiceNo>10248</InvoiceNo>     <OrderDate>1996-07-04T00:00:00</OrderDate>     <Item>         <ProductID>11</ProductID>         <Price>14</Price>         <Quantity>12</Quantity>     </Item>     <Item>          <ProductID>42</ProductID>         <Price>9.8</Price>         <Quantity>10</Quantity>     </Item>     <Item>         <ProductID>72</ProductID>         <Price>34.8</Price>         <Quantity>5</Quantity>     </Item> </Invoice> 

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

The ELEMENTS option is an all-or-nothing choice; either all columns are returned as elements or all columns are returned as attributes. You can’t use AUTO mode to retrieve XML containing a mixture of element-centric and attribute-centric mappings.

AUTO mode’s greater control over the format of the XML returned means that it allows you to retrieve more flexible document structures than RAW mode does. However, GROUP BY queries and aggregate functions aren’t supported in AUTO mode, so if you need aggregate data in an XML document, you might want to stick with RAW mode.