asked
Add attribute at ROOT('')
Hi, On MS SQL 2012/Windows 2008/R2, is there a way I could add Name (from first column) after root tag when creating xml: SELECT ***Name*** ,ValueType FROM dbo.#m attr WHERE FOR XML PATH ('Attribute'), **ROOT('MappingAttribute'),** TYPE) So result would add Name to MappingAttribute tag: ***change to ---> *** Customer role thanks, Chris
@Chris 2 Please provide some sample data and expected result. This way it will be easier to come up with the answer. It is very easy to add the attribute to the root node, but the question needs to be clarified. It looks like you would like to add some attribute to the root node named MappingAttribute. Because there is only one occurrence of this tag in the resulting XML (for it is the root node), it is not clear what value you would like to assign to that attribute. Is this the value of the first record's Name column? Or is this some hardcoded value, such as "Customer role"? What is the name of the attribute? Please clarify. Thank you.
This solution might require some modification depending on the requirements. If there is a need to add an attribute to the root node of the XML then it could be done by simply nesting the actual select which returns XML data minus the root node itself. For example, select 'Customer role' [@Name], ('Some xml nodes go here') for xml path('MappingAttribute'); -- the above query returns Some xml nodes go here From the query above it is easy to see that the original query (without the root specification though) can be nested in the same way: select 'Customer role' [@Name], ( select [Name] ,ValueType from #m attr for xml path('Attribute'), type ) for xml path('MappingAttribute'); This query will return XML consisting of the desired root node now including the attribute and the original nodes as per select statement in question. If the value of the root node's attribute needs to be something else in place of the hardcoded value then this part of the select will need to be modified accordingly. For example, the select statement below will pick the value of the Name column of the first record in #m table and populate the root node's attribute value: select top 1 [Name] [@Name], ( select [Name] ,ValueType from #m attr for xml path('Attribute'), type ) from #m order by [Name] for xml path('MappingAttribute'); Hope this helps. Oleg
18 People are following this question.
