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

Well, the title of this post is so long, I almost don't need to reconstruct Steve's question, but here it is..

I need to construct a FOR XML EXPLICIT statement that essentially produces something like

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

I can create elements and I can create attributes but I can’t seem to be able to create an attribute on an element that has a value. Thanks for the help.

The solution involves use of the “Element” directive in the fourth (optional) portion of the XML column name:

select 1 as tag,
0 as parent,
NULL as [Product!1!],
NULL as [Category!2!rank],
NULL as [Category!2!!Element]

union all

select

2 as tag,
1 as parent,
NULL as [Product!1!],
'0' as [Category!2!rank],
'Category Name' as [Category!2!!Element]

for

xml explicit

This is an application of a technique I found described here on MSDN.

     -wp