AUTO Mode
| With the exception of the previous section, all our dealings with the FOR XML extension have been related to AUTO mode. In this section, we'll talk about this mode some more and will introduce some new things, including some unexpected quirks . XML AUTO returns result in documents with nested elements. We've never really discussed how the elements are nested with respect to the query used to generate the results. Here goes: Each table in the FROM clause with at least one column is listed in the SELECT clause, and is represented as an XML element. Element nesting in the resulting document depends on the order in which the columns are listed in the SELECT clause; the leftmost table defines the first element, the next table defines the first nested element, and so on. The columns listed in the SELECT clause are mapped to the appropriate element as attributes. If the column name is from a table already identified by a previous column in the SELECT clause, it also becomes an attribute. Consider the following SQL statement illustrating nesting rules: SELECT Orders.CustomerID, [Order Details].UnitPrice,Orders.OrderDate FROM Orders, [Order Details] WHERE Orders.OrderID = [Order Details].OrderID FOR XML AUTO The results of the rules are illustrated in Listing 7.9. Notice that the Orders table was mentioned first, so it's the first element. The Order Details table is mentioned next, so it becomes the first nested element. Because the Orders table was already mentioned as the first quantity of the SELECT statement, the OrderDate becomes an attribute of the Orders element. Listing 7.9 Partial Results of Element Nesting Rules Example<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders CustomerID="VINET" OrderDate="1996-07-04T00:00:00"> <Order_x0020_Details UnitPrice="14" /> <Order_x0020_Details UnitPrice="9.8" /> <Order_x0020_Details UnitPrice="34.8" /> </Orders> <Orders CustomerID="TOMSP" OrderDate="1996-07-05T00:00:00"> <Order_x0020_Details UnitPrice="18.6" /> <Order_x0020_Details UnitPrice="42.4" /> </Orders> ... Listing 7.9 demonstrates the attribute-centric form of FOR XML AUTO, in which all data entities are assigned to attributes of the related element. By specifying the ELEMENTS argument, the returned document can be made element-centric , which places data entities as nested elements of the related element. Which parameter you choose usually depends on the situation. Listing 7.10 is a partial results listing of the SQL statement for nesting rules with the addition of the ELEMENTS argument as follows : FOR XML AUTO , ELEMENTS . Listing 7.10 Partial Results of Adding the ELEMENTS Argument<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders> <CustomerID>VINET</CustomerID> <OrderDate>1996-07-04T00:00:00</OrderDate> <Order_x0020_Details> <UnitPrice>14</UnitPrice> </Order_x0020_Details> <Order_x0020_Details> <UnitPrice>9.8</UnitPrice> </Order_x0020_Details> <Order_x0020_Details> <UnitPrice>34.8</UnitPrice> /Order_x0020_Details> </Orders> <CustomerID>TOMSP</CustomerID> <OrderDate>1996-07-05T00:00:00</OrderDate> <Order_x0020_Details> ... Primary key fields play a role in the nesting of the resulting XML document also. There is a one-to-one correspondence between a change in the primary key field and a change in the new element. Consider the following SQL statement illustrating primary key usage: SELECT Orders.OrderID, Orders.CustomerID, [Order Details].UnitPrice,Orders.OrderDate FROM Orders,[Order Details] WHERE Orders.OrderID = [Order Details].OrderID FOR XML AUTO If the OrderID is the primary key, then the results returned are shown in Listing 7.11. Listing 7.11 Partial Results of OrderID Being the Primary Key<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders OrderID="10248" CustomerID="VINET" OrderDate="1996-07-04T00:00:00"> <Order_x0020_Details UnitPrice="14" /> <Order_x0020_Details UnitPrice="9.8" /> <Order_x0020_Details UnitPrice="34.8" /> </Orders> <Orders OrderID="10249" CustomerID="TOMSP" OrderDate="1996-07-05T00:00:00"> <Order_x0020_Details UnitPrice="18.6" /> <Order_x0020_Details UnitPrice="42.4" /> </Orders> <Orders OrderID="10250" CustomerID="HANAR" OrderDate="1996-07-08T00:00:00"> ... Notice in Listing 7.11 that every new OrderID starts a new Orders element. This is due to OrderID being the primary key. Whenever it changes, a new element is created. Let's assume that OrderID , in this case, was not a primary key. The output would be totally different. In the absence of a primary key, every field is examined for changes. If any of the fields change, a new element is created. Table Name AliasesWe worked earlier with a couple of examples of using aliases for table names . The following example shows our SQL query using aliases, and Listing 7.12 gives the results. SELECT o.OrderID, o.CustomerID, od.UnitPrice,o.OrderDate FROM Orders o, [Order Details] od WHERE o.OrderID = od.OrderID FOR XML AUTO Listing 7.12 Partial Results of Utilizing Aliases<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <o OrderID="10248" CustomerID="VINET" OrderDate="1996-07-04T00:00:00"> <od UnitPrice="14" /> <od UnitPrice="9.8" /> <od UnitPrice="34.8" /> </o> <o OrderID="10249" CustomerID="TOMSP" OrderDate="1996-07-05T00:00:00"> <od UnitPrice="18.6" /> <od UnitPrice="42.4" /> </o> <o OrderID="10250" CustomerID="HANAR" OrderDate="1996-07-08T00:00:00"> ... Nothing is new here; the o is substituted for the Orders element, and od takes the place of Order Details. We're looking at this again for one reason: nested queries. The problem is that the query optimizer doesn't guarantee that aliases used in the inner query of nested queries will keep their alias. The following example shows the SQL statement giving partial results of using aliases; Listing 7.13 shows the resulting XML. SELECT TOP 3 * FROM (SELECT * FROM Orders) as ORD FOR XML AUTO Listing 7.13 Partial Results of the Nested Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders CustomerID="VINET" /> <Orders CustomerID="TOMSP" /> <Orders CustomerID="HANAR" /> </ROOT> Notice how the element that we wanted to be called <ORD> was changed to <Orders> . There's no workaround for this. You just have to live with it. GROUP BY and Aggregate FunctionsIn the "FOR XML Limitations" section at the beginning of this chapter, I mentioned that aggregate functions and the GROUP BY expression are not allowed with FOR XML. This is still true, but there is a workaround for these limitations, nested queries or derived tables as I called them earlier (here we go again). Basically, the inner query does the aggregate and grouping operations, creating a table that contains these different values, and then it passes them to the outer query. Listings 7.14 and 7.15 show a sample SQL statement and its results. Listing 7.14 SQL to Work Around Aggregate LimitationsSELECT Emp.LastName, Emp.EmployeeID, TotalOrders, O.OrderID, O.CustomerID, OD.ProductID, OD.Quantity FROM ( SELECT E.EmployeeID, E.LastName, count(*) as TotalOrders FROM Employees E left outer join Orders O ON E.EmployeeID = O.EmployeeID GROUP BY E.EmployeeID, E.LastName ) AS Emp left outer join Orders O on Emp.EmployeeID = O.EmployeeID left outer join [Order Details] OD on O..OrderID = OD.OrderID FOR XML AUTO Listing 7.15 Partial Results of the Workaround<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Emp LastName="Davolio" EmployeeID="1" TotalOrders="123"> <O OrderID="10258" CustomerID="ERNSH"> <OD ProductID="2" Quantity="50" /> <OD ProductID="5" Quantity="65" /> <OD ProductID="32" Quantity=="6" /> </O> <O OrderID="10270" CustomerID="WARTH"> <OD ProductID="36" Quantity="30" /> <OD ProductID="43" Quantity="25" /> </O> <O OrderID="10275" CustomerID="MAGAA"> <OD ProductID="24" Quantity="12" /> <OD ProductID="59" Quantity="6" /> </O> ... Computed ColumnsComputed columns are another facet of the FOR XML extension that shouldn't present any surprises . Listing 7.16 shows an example and Listing 7.17 shows the results of a computed column, Contact . We combine ContactName , a space, and then ContactTitle . Listing 7.16 Computed Column SQL QuerySELECT ContactName + ' ' ++ ContactTitle as Contact, Orders.OrderID FROM Customers, Orders WHERE Orders.CustomerID = Customers.CustomerID ORDER BY Contact FOR XML AUTO Listing 7.17 Partial Results of the Computed Column Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders Contact="Alejandra Camino Accounting Manager" OrderID="10281" /> <Orders Contact="Alejandra Camino Accounting Manager" OrderID="10282" /> <Orders Contact="Alejandra Camino Accounting Manager" OrderID="10306" /> <Orders Contact="Alejandra Camino Accounting Manager" OrderID="10917" /> <Orders Contact="Alejandra Camino Accounting Manager" OrderID="11013" /> <Orders Contact="Alexander Feuer Marketing Assistant" OrderID="10277" /> <Orders Contact="Alexander Feuer Marketing Assistant" OrderID="10575" /> ... If you want to make the Contact attribute its own element, you can rewrite the query to contain a subquery that generates a table called Contact containing the Name element, and then you can specify the name element of the Contact table first in the outer query. This is illustrated in Listings 7.18 and 7.19. Listing 7.18 Rearranging Elements via a SubquerySELECT Contact.Name, Orders.OrderID FROM (SELECT ContactName + ' ' ++ ContactTitle as Name, CustomerID FROM Customers) Contact LEFT OUTER JOIN Orders ON Contact.CustomerID = Orders.CustomerID ORDER BY Contact.Name FOR XML AUTO Listing 7.19 Partial Results of the Subquery<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Contact Name="Alejandra Camino Accounting Manager"> <Orders OrderID="10281" /> <Orders OrderID="10282" /> <Orders OrderID="10306" /> <Orders OrderID="10917" /> <Orders OrderID="11013" /> </Contact> <Contact Name="Alexander Feuer Marketing Assistant"> ... |
