Michael Rys

Moving FOR XML EXPLICIT to the new PATH mode (part II)

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]:

posted on Thursday, February 19, 2004 8:34 PM by mrys