Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)


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

  • Comments 5
  • Likes

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.


Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment