In the previous post, I looked at how we can utilize the new SQL Server Yukon capabilities to rewrite a simple FOR XML EXPLICIT query into a simpler one that exploits the nestability of FOR XML and the new XML datatype.
However, the previous query was too simple, you may say. One of the strengths of the explicit mode is to mix attributes and elements at will, create wrappers and nested, complex properties and even create space separated value lists and mixed content. None of these results can be achieved by nesting FOR XML AUTO queries. But don't despair. In Yukon (Beta2), we have added a new FOR XML mode that gives you the same flexibilty in a much simpler way.
The new PATH mode allows you to use an XPath-like syntax as a column name which then is mapped into an attribute (e.g., @a), element (e.g., e), subelement structure (e1/e2), element content (*), text node (text()), or datavalue (data()). As with the RAW mode, the default name for the row element is row. And in Beta2, you will be able to override the name in either mode.
So let me give some examples. First let's give the PATH mode formulation of the EXPLICIT mode query:
SELECT CustomerID as "@CustomerID",
(SELECT OrderID as "@OrderID"
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
FOR XML PATH('Order'), TYPE),
(SELECT DISTINCT LastName as "@LastName"
FROM Employees
JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML PATH('Employee'), TYPE)
FROM Customers
FOR XML PATH('Customer')
This one is similar to the AUTO mode version. Now let's look at some of the PATH mode specific capabilities. The next query takes the customer information and groups the address and contact information into separate subelements and - using the new ROOT directive - adds a root node around it for good measure. Bryant, I think that will answer your question :-):
SELECT
CustomerID as "@CustomerID",
CompanyName,
Address as "address/street",
City as "address/city",
Region as "address/region",
PostalCode as "address/zip",
Country as "address/country",
ContactName as "contact/name",
ContactTitle as "contact/title",
Phone as "contact/phone",
Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer'), ROOT('doc')
Now let's look at what we have to write when using the EXPLICIT mode. We need four select clauses again (one for each non-leaf element):
SELECT top 1
1 as TAG,
NULL as Parent,
1 as "doc!1!dummy!hide",
NULL as "Customer!2!CustomerID",
NULL as "Customer!2!CompanyName!element",
NULL as "address!3!street!element",
NULL as "address!3!city!element",
NULL as "address!3!region!element",
NULL as "address!3!zip!element",
NULL as "address!3!country!element",
NULL as "contact!4!name!element",
NULL as "contact!4!title!element",
NULL as "contact!4!phone!element",
NULL as "contact!4!fax!element"
FROM Customers
UNION ALL
SELECT 2, 1,
1,
CustomerID, CompanyName,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 3, 2,
1,
CustomerID, NULL,
Address, City, Region, PostalCode, Country,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 4, 2,
1,
CustomerID, NULL,
NULL, NULL, NULL, NULL, NULL,
ContactName, ContactTitle, Phone, Fax
FROM Customers
ORDER BY "doc!1!dummy!hide","Customer!2!CustomerID"
FOR XML EXPLICT, TYPE
I think, I know what I prefer to write :-).
Last but not least, the following gives us an example of generating a value list and show the use of a text node.
select
CustomerID as '@ID',
(select orderid as 'data()'
from orders
where customers.CustomerID=orders.CustomerID
FOR XML PATH('')
) as '@OrderIDs',
CompanyName,
ContactTitle as 'ContactName/@ContactTitle',
ContactName as 'ContactName/text()',
PostalCode as 'Address/@ZIP',
Address as 'Address/Street',
City as 'Address/City'
FROM
customers
FOR XML PATH('Customer')
This creates a result of the form [this has been added in a later edit]:
<Customer ID="HUNGC" OrderIDs="10375 10394 10415 10600 10660">
<CompanyName>Hungry Coyote Import Store</CompanyName>
<ContactName ContactTitle="Sales Representative">Yoshi Latimer</ContactName>
<Address ZIP="97827">
<Street>City Center Plaza 516 Main St.</Street>
<City>Elgin</City>
</Address>
</Customer>
Please let me know if this article was useful and what else you want more information about. The next article later this week will be about another topic close to my heart: should the type expression element(name) in XQuery be tied to a schema or not...
In the following, I would like to look at a FOR XML EXPLICIT mode query and discuss how Yukon will be able to help you to write the query in a simpler way. Since I have not yet received any challenging queries, it will be a simple query based on the Northwind database. A previous post provides some more information about the rowset-aggregation functionality of FOR XML in general.
Now I know, that only a select few can make use of these features yet (the PATH mode is part of the Beta2 functionality), but I want to start having you think about how you can start utilizing the new functionality. Once you will have access to beta2, we are looking at feedback on how you like the new functionality and also whether you see any performance and scaling issues compared to the EXPLICIT mode (we are doing tests and work in this area, but real life feedback is very valuable).
So let's look at the example. The following FOR XML EXPLICIT query is returning Customer elements containing their orders and the employees that work on their orders. For simplification, we are only returning a single property per element.
SELECT 1 as TAG,
NULL as Parent,
CustomerID as "Customer!1!CustomerID",
NULL as "Order!2!OrderID",
NULL as "Employee!3!LastName"
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID,
NULL
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
UNION ALL
SELECT DISTINCT 3,
1,
Customers.CustomerID,
NULL,
Employees.LastName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY "Customer!1!CustomerID","Employee!3!LastName","Order!2!OrderID"
FOR XML Explicit