SQL Server generating XML with generic field elements

Question

I'm basically trying to reverse what this question is asking... SQL Server query xml attribute for an element value

I need to produce a result set of "row" elements that contain a group of "field" elements with an attribute that defines the key.

<resultset statement="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
    <field name="id">1</field>
    <field name="version”>0</field>
    <field name="property">My Movie</field>
    <field name="release_date">2012-01-01</field>
    <field name="territory_code”>FR</field>
    <field name="territory_description">FRANCE</field>
    <field name="currency_code”>EUR</field>
</row>
<row>
    <field name="id">2</field>
    <field name="version”>0</field>
    <field name="property">My Sequel</field>
    <field name="release_date">2014-03-01</field>
    <field name="territory_code”>UK</field>
    <field name="territory_description">United Kingdom</field>
    <field name="currency_code”>GBP</field>
</row>
</resultset>

I've got a query that returns this...

<resultset statement="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
    <id>1</id>
    <version>0</version>
    <property>My Movie</property>
    <release_date>2012-01-01</release_date>
    <territory_code>FR</territory_code>
    <territory_description>FRANCE</territory_description>
    <currency_code>EUR</currency_code>
</row>
<row>
    <id>2</id>
    <version>0</version>
    <property>My Sequel</property>
    <release_date>2014-03-01</release_date>
    <territory_code>UK</territory_code>
    <territory_description>UNITED KINGDOM</territory_description>
    <currency_code>GBP</currency_code>
</row>
</resultset>

Using FOR XML PATH ('row'), ROOT ('resultset') in my SQL statement.

What am I missing? Thanks.

score 9 · Accepted Answer · edited Aug 21 '14 at 05:59

It's a bit involved in SQL Server - the normal behavior is what you're seeing - the column names will be used as XML element names.

If you really want all XML elements to be named the same, you'll have to use code something like this:

SELECT
    'id' AS 'field/@name',
    id AS 'field',
    '',
    'version' AS 'field/@name',
    version AS 'field',
    '',
    'property' AS 'field/@name',
    property AS 'field',
    '',
    ... and so on ....
FROM Person.Person
FOR XML PATH('row'),ROOT('resultset')

This is necessary to make sure the column name is used as the name attribute on the <field> element, and the empty string are necessary so that the SQL XML parser doesn't get confused about which name attribute belongs to what element......

I was afraid something like that was going to be the answer! It worked like a charm though, thanks for the quick response. — HenryC, Aug 20 '14 at 22:04

score 1 · Answer 2 · answered Aug 21 '14 at 05:58

You can do this without having to specify the columns as constants and that will allow you to also use select *. It is a bit more complicated than the answer provided by marc_s and it will be quite a lot slower to execute.

select (
       select T.X.value('local-name(.)', 'nvarchar(128)') as '@name',
              T.X.value('text()[1]', 'nvarchar(max)') as '*'
       from C.X.nodes('/X/*') as T(X)
       for xml path('field'), type
       )
from (
     select (
            select T.*
            for xml path('X'), type
            ) as X
     from dbo.YourTable as T
     ) as C
for xml path('row'), root('resultset')

SQL Fiddle

The query creates a derived table where each row has a XML that looks something like this:

<X>
  <ID>1</ID>
  <Col1>1</Col1>
  <Col2>2014-08-21</Col2>
</X>

That XML is then shredded using nodes() and local-name(.) to create the shape you want.

score -1 · Answer 3 · answered Aug 20 '14 at 19:11

-1

Your SELECT statement needs to look something like this

SELECT
    'id' AS [field/@name],
    id AS field,
    'version' AS [field/@name],
    version AS field,
    'property' AS [field/@name],
    property AS field,
    'release_date' AS [field/@name],
    release_date AS field,
    'territory_code' AS [field/@name],
    territory_code AS field,
    'territory_description' AS [field/@name],
    territory_description AS field,
    'currency_code' AS [field/@name],
    currency_code AS field

answered Aug 20 '14 at 19:11

Ahz

361
1
2
6

This will **not** work as is..... you'll get an error: `Msg 6852, Level 16, State 1, Line 1 - Attribute-centric column 'field/@name' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.` – marc_s Aug 20 '14 at 19:11
You're right. I wasn't able to run the statement against anything to validate syntax. – Ahz Aug 20 '14 at 19:20

SQL Server generating XML with generic field elements

3 Answers3

Linked