Examples: Using AUTO Mode
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.
Example: 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>
Example: 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" />
...
Example: 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>
...
...
Example: 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.
Example: 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.