Database Programming: Buliding a FOR XML PATH Statement Containing Both an Element Value and an Attribute Associated With The Element

  • Article

On Friday afternoon, I shared an answer to Steve's question seeking a FOR XML EXPLICIT query to produce this XML:

<Product>
<Category rank="0">Category Name</Category>
</Product>

Well, over the weekend my "geek beanie" went all atwirl wondering if this XML could also be produced via a FOR XML PATH statement. It took a little poking around in BOL (the article that ended up helping me was here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a685a9ad-3d28-4596-aa72-119202df3976.htm), but I finally came up with this:

select

  '0' as '@rank',
        'Category Name' as '*'
for xml path('Category'), root('Product'), type

The "*" produces an element with no name as Steve seeks.

It should be noted that the two queries "cost out" the same, but product different query plans.

The query plan for the FOR XML EXPLICIT:

  |--Constant Scan(VALUES:(((1),(0),NULL,NULL,NULL),((2),(1),NULL,'0','Category Name')))

The query plan for the FOR XML PATH:

  |--UDX(([Expr1000], [Expr1001]))
|--Constant Scan(VALUES:(('0','Category Name')))

It might be interesting to test the two syntaxes under a more representative load/structure, as there may be some performance implications here for certain scenarios. I will certainly file this away for further research.

I'm going to finish my lunch now.. :)

     -wp