Using AUTO Mode
As described in the topic, Retrieving XML Documents Using FOR XML, AUTO mode returns query results as nested XML elements. This does not provide much control over the shape of the XML generated from a query result. The AUTO mode queries are useful if you want to generate simple hierarchies. However, Using EXPLICIT Mode and Using PATH Mode provide more control and flexibility in deciding the shape of the XML from a query result.
Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified in the FOR XML clause.
The XML hierarchy, nesting of the elements, in the resulting XML is based on the order of tables identified by the columns specified in the SELECT clause. Therefore, the order in which column names are specified in the SELECT clause is significant. The first, leftmost table that is identified forms the top element in the resulting XML document. The second leftmost table, identified by columns in the SELECT statement, forms a subelement within the top element, and so on.
If a column name listed in the SELECT clause is from a table that is already identified by a previously specified column in the SELECT clause, the column is added as an attribute of the element already created, instead of opening a new level of hierarchy. If the ELEMENTS option is specified, the column is added as an attribute.
For example, execute this query:
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
This is the partial result:
<Cust CustomerID="1" CustomerType="S">
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...
Note the following in the SELECT clause:
- The CustomerID references the Cust table. Therefore, a <
Cust> element is created and CustomerID is added as its attribute. - Next, three columns, OrderHeader.CustomerID, OrderHeader.SaleOrderID, and OrderHeader.Status, reference the OrderHeader table. Therefore, an <
OrderHeader> element is added as a subelement of the <Cust> element and the three columns are added as attributes of <OrderHeader>. - Next, the Cust.CustomerType column again references the Cust table that was already identified by the Cust.CustomerID column. Therefore, no new element is created. Instead, the CustomerType attribute is added to the <
Cust> element that was previously created. - The query specifies aliases for the table names. These aliases appear as corresponding element names.
- ORDER BY is required to group all children under one parent.
This query is similar to the previous one, except the SELECT clause specifies columns in the OrderHeader table before the columns in the Cust table. Therefore, first <OrderHeader> element is created and then the <Cust> child element is added to it.
select OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerID,
Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto
This is the partial result:
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
<Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...
If the ELEMENTS option is added in the FOR XML clause, element-centric XML is returned.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
This is the partial result:
<Cust>
<CustomerID>1</CustomerID>
<CustomerType>S</CustomerType>
<OrderHeader>
<CustomerID>1</CustomerID>
<SalesOrderID>43860</SalesOrderID>
<Status>5</Status>
</OrderHeader>
...
</Cust>
...
In this query, the CustomerID values are compared from one row to the next in creating the <Cust> elements, because CustomerID is the primary key of the table. If CustomerID is not identified as the primary key for the table, all the column values (CustomerID, CustomerType in this query) are compared from one row to the next. If the values differ, a new <Cust> element is added to the XML.
When comparing these column values, if any of the columns to be compared are of type text, ntext, image, or xml, FOR XML assumes that the values are different and not compared, even though they may be the same. This is because comparing large objects is not supported. Elements are added to the result for each row selected. Note that columns of (n)varchar(max) and varbinary(max) are compared.
When a column in the SELECT clause cannot be associated with any of the tables identified in the FROM clause, as in the case of an aggregate column or computed column, the column is added in the XML document in the deepest nesting level in place when it is encountered in the list. If such a column appears as the first column in the SELECT clause, the column is added to the top element.
If the asterisk (*) wildcard character is specified in the SELECT clause, the nesting is determined in the same way as previously described, based on the order that the rows are returned by the query engine.
If the BINARY BASE64 option is specified in the query, the binary data is returned in base64 encoding format. By default, if the BINARY BASE64 option is not specified, AUTO mode supports URL encoding of binary data. That is, instead of binary data, a reference to a relative URL to the virtual root of the database where the query was executed is returned. This reference can be used to access the actual binary data in subsequent operations by using the SQLXML ISAPI dbobject query. The query must provide enough information, such as primary key columns, to identify the image.
In specifying a query, if an alias is used for the binary column of the view, the alias is returned in the URL encoding of the binary data. In subsequent operations, the alias is meaningless, and the URL encoding cannot be used to retrieve the image. Therefore, do not use aliases when querying a view using FOR XML AUTO mode.
Understanding AUTO Mode Heuristics in Shaping Returned XML
AUTO mode determines the shape of returned XML based on the query. In determining how elements are to be nested, AUTO mode heuristics compare column values in adjacent rows. Columns of all types, except ntext, text, image, and xml, are compared. Columns of type (n)varchar(max) and varbinary(max) are compared.
The following example illustrates the AUTO mode heuristics that determine the shape of the resulting XML:
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
To determine where a new <T1> element starts, all column values of T1, except ntext, text, image and xml, are compared if the key on the table T1 is not specified. Next, assume that the Name column is nvarchar(40) and the SELECT statement returns this rowset:
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
The AUTO mode heuristics compare all the values of table T1, the Id and Name columns. Because the first two rows have the same values for the Id and Name columns, one <T1> element having two <T2> child elements is added in the result.
Following is the XML that is returned:
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Now assume that the Name column is of text type. The AUTO mode heuristics do not compare the values for this type. Instead, it assumes that the values are not the same. This results in XML generation as shown in the following:
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Examples
The following examples illustrate the use of AUTO mode. Many of these queries are specified against bicycle manufacturing instructions XML documents that are stored in the Instructions column of the ProductModel table. For more information about the XML instructions, see xml Data Type Representation in the AdventureWorks Database.
A. Retrieving customer, order, and order detail information
This query retrieves customer, order, and order detail information for a specific customer.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
Because the query identifies, Cust, OrderHeader, Detail, and Product table aliases, corresponding elements are generated by the AUTO mode. Again, the order in which tables are identified by the columns specified in the SELECT clause determine the hierarchy of these elements.
This is the partial result.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
B. Specifying GROUP BY and aggregate functions
The following query returns individual customer IDs and the number of orders that the customer has requested.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
This is the partial result:
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
C. Specifying computed columns in AUTO mode
This query returns concatenated individual customer names and the order information. Because the computed column is assigned to the innermost level encountered at that point, the <SOH> element in this example. The concatenated customer names are added as attributes of the <SOH> element in the result.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
This is the partial result:
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
To retrieve the <IndividualCustomer> elements having the Name attribute that contains each sales order header information as a subelement, the query is rewritten using a sub select. The inner select creates a temporary IndividualCustomer table with the computed column that contains the names of the individual customers. This table is then joined to the SalesOrderHeader table to obtain the result.
Note that the Sales.Individual table stores individual customer information, including the ContactID value for that customer. This ContactID is then used to find the contact name from the Person.Contact table.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
This is the partial result:
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
D. Returning binary data
This query returns an employee photo from the Employees table. Photo is an image column in the Employees table. By default, AUTO mode returns to the binary data a reference that is a relative URL to the virtual root of the database where the query is executed. The EmployeeID key attribute must be specified to identify the image. In retrieving an image reference as illustrated in this example, the primary key of the table must also be specified in the SELECT clause to uniquely identify a row.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
This is the result:
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
The same query is executed with the BINARY BASE64 option. The query returns the binary data in base64-encoded format.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
This is the result:
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
By default, when you use AUTO mode to retrieve binary data, a reference to a relative URL to the virtual root of the database where the query was executed will be returned instead of the binary data. This will occur if the BINARY BASE64 option is not specified.
When AUTO mode returns a URL reference to the binary data in case-insensitive databases where a table or column name specified in the query does not match the table or column name in the database, the query executes. However, the case returned in the reference will not be consistent. For example:
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
This is the result:
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
This can be a problem particularly when dbobject queries are executed against a case sensitive database. To avoid this, the case of the table or column name that is specified in the queries should match the case of the table or column name in the database.
E. Understanding the encoding
This example shows the various encoding that occurs in the result.
Create this table:
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
Add the following data to the table:
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
This query returns the data from the table. The FOR XML AUTO mode is specified. Binary data is returned as a reference.
SELECT * FROM [Special Chars] FOR XML AUTO
This is the result:
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
This is the process for encoding special characters in the result:
- In the query result, the special XML and URL characters in the element and attribute names that are returned are encoded by using the hexadecimal value of the corresponding Unicode character. In the previous result, the element name <
Special Chars> is returned as <Special_x0020_Chars>. The attribute name <Col#&2> is returned as <Col_x0023__x0026_2>. Both XML and URL special characters are encoded. - If the values of the elements or attribute contain any of the five standard XML character entities (', "", <, >, and &), these special XML characters are always encoded using XML character encoding. In the previous result, the value & in the value of attribute <
Col1> is encoded as &. However, the # character remains #, because it is a valid XML character and not a special XML character. - If the values of the elements or attributes contain any special URL characters that have special meaning in the URL, they are encoded only in the DBOBJECT URL value and are encoded only when the special character is part of a table or column name. In the result, the character # that is part of table name Col#&2 is encoded as _x0023_ in the DBOJBECT URL.
See Also
Reference
Constructing XML Using FOR XML