Moving FOR XML EXPLICIT to the new PATH mode (part I)
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
As you can see, we need a select statement for each element. We also repeat the parent's identification, so that the order by groups the children with its parent.
Now let's look how we can rewrite this using nesting. In Yukon, we now can leverage the XML datatype with FOR XML in two ways: We can use it to add more subhierarchies and can generate it as a result of a FOR XML query. In order to generate an XML result, we need to use the new TYPE directive. Otherwise, you will get a textual result that will be entitized if it is embedded in another FOR XML query. The reason for the TYPE directive is backwards-compatibility with FOR XML's ability to generate non-wellformed XML.
So we can now use a separate FOR XML query for every of the three “entity” elements and nest them to express the hierarchy. In the following, I am using FOR XML AUTO to rewrite the EXPLICIT mode query. This query should work with the Beta 1 build. In a later post, I will introduce the PATH mode by using the mode for the same and some more complex queries.
SELECT CustomerID as "CustomerID",
(SELECT OrderID as "OrderID"
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
FOR XML AUTO, TYPE),
(SELECT DISTINCT LastName as "LastName"
FROM Employees
JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, TYPE)
FROM Customers
FOR XML AUTO, TYPE)
Obviously, this query is easier to write, understand and maintain. OTOH, due to the fact that we create intermediate XML objects, it may have worse performance characteristics (altough, sometimes it can be faster since we do not need the union and less joins).