SQL Server Generates XML Using Common Field Elements

Question

I'm basically trying to undo what this question asks ... SQL Server XML query attribute for element value

I need to create a result set from "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 have 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.

Answer 1

This has a bit to do with SQL Server - normal behavior - this is what you see - column names will be used as the names of the XML elements.

If you really want all the XML elements to be named the same, you will need to use the 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 that the column name is used as the name attribute in the <field> element, and an empty string is needed so that the SQL XML parser is not confused about which name attribute belongs to that element ...

Answer 2

You can do this without specifying the columns as constants, and this will allow you to use select * as well. This is a bit more complicated than the answer provided by marc_s, and it will be much slower to execute.

 select ( select TXvalue('local-name(.)', 'nvarchar(128)') as '@name', TXvalue('text()[1]', 'nvarchar(max)') as '*' from CXnodes('/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 view in which each row has XML that looks something like this:

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

This XML is then ground using nodes() and local-name(.) To create the desired form.

Answer 3

Your SELECT statement should 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