Extreme JS

JS Greenwood's WebLog on architecture, .NET, processes, and life...

XML Explicit

On various MS code help forums I've seen dozens of posts along the lines of "How do I get a root tag in an FOR XML EXPLICIT statement.  There have been numerous responses:
Do three SELECTs, with a "SELECT '<root>' FROM [Foo]" as the first one
Read the data back into one string, and append the opening/closing tag
Etc. etc.

These all feel like "hacks" to get round the real issue - SQL Server creates structured XML by converting flat, tabular data into a hierarchy using level-indices (i.e. an integer from 0..N).  All we actually need to do is create a single element at a level higher.  So, given the following SQL (Run against Northwind):

SELECT
  1            AS Tag,
  NULL         AS Parent,
  [CustomerID] AS [customer!1!customerid]

FROM
  [Customers]

FOR XML EXPLICIT

We can add a root element of "customers" by adding the following preceding SQL:

SELECT
  1    AS Tag,
  0    AS Parent,
  NULL AS [customers!1!foo],
  NULL AS [customer!2!customerid]

UNION ALL
  SELECT
  2            AS Tag,
  1            AS Parent,
  NULL,
  [CustomerID] AS [customer!2!customerid]

FROM
  [Customers]

FOR XML EXPLICIT

To see how this tabular -> hierarchical conversion takes place, simply omit the FOR XML EXPLICIT statement from the end of each statement when run in Query Analyzer.

Posted: Jul 05 2004, 08:20 PM by jsgreenwood | with 2 comment(s)
Filed under:

Comments

Evandro said:

Good example! Thanks

# April 8, 2008 5:15 PM

Ahmed Barakat said:

Thanks, I got here through the google search "FOR XML EXPLICIT root node". That was what I was looking for exactly :)

# May 15, 2008 6:58 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)