How to add attributes to xml nodes in sql server 2005

Question

If i wanted to add an attribute to the root element record, can i do this from the sql side?

SELECT top 1 'text' as nodeA
                from test as z
FOR XML AUTO, ELEMENTS, root('record')

i would like to produce the xml like this:

 <Root attribute="value">
     <z>
         <NodeA>text</NodeA>
     </z>
 </Root>

Answer 1

Use the new FOR XML PATH syntax:

SELECT TOP 1 
   'someValue' AS '@Attribute',
   'text' as 'z/NodeA'
FROM dbo.Test
WHERE....
FOR XML PATH('YourElement'), ROOT('Root')

This would give something like

<Root>
   <YourElement Attribute="someValue">
      <z>
         <NodeA>text</NodeA>
      </z>
   </YourElement>
</Root>

Read more about it here:

• Simple Example of Creating XML File Using T-SQL
• Using XML Serialization with SQL's FOR XML PATH

Answer 2

Your example is not doing what is requested.

request:

<Root attribute="someValue">
   <YourElement>
      <z>
         <NodeA>text</NodeA>
      </z>
   </YourElement>
</Root>

your answer:

<Root>
   <YourElement Attribute="someValue">
      <z>
         <NodeA>text</NodeA>
      </z>
   </YourElement>
</Root>

I'm doing something similar and using PowerShell to scrub the file before saving it:

scrub reason 1: https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

scrub reason 2: THIS