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

Category rank=0>Category Name</Category>

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.


Comments (4)

  1. Anonymous says:

    On Friday afternoon, I shared an answer to Steve’s question seeking a FOR XML EXPLICIT query to produce…

  2. Anonymous says:

    Great post, explained really well and I could really understand. Thank you.

  3. Sergey Belov says:

    Please correct me if I am wrong, but it seems the element directive is not necessary here at all. I explained the details in my post: