CODEWITHSUNDEEP
sqlsql-serverxml
HenryC
HenryC

Reputation: 409

SQL Server generating XML with generic field elements

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.

Upvotes: 8

Views: 2137

Answers (3)

marc_s
marc_s

Reputation: 754658

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......

Upvotes: 9

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

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.

Upvotes: 1

Ahz
Ahz

Reputation: 361

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

Upvotes: -1

Related Questions