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

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: http://codersperspective.blogspot.com/2006/09/for-xml-explicit-directive-part.html