CODEWITHSUNDEEP
sqlsql-serverxmlxpath
A Johnston
A Johnston

Reputation: 33

SQL FOR XML Path, returning multiple child elements

I require data to be returned from table in following format.

<Root>
  <Property name="test1">text1</Property>
  <Property name="test2">text2</Property>
  <Property name="test3">text3</Property>
  <Property name="test4">text4</Property>
</Root>

I've tried the code from other post SQL Server for xml path add attributes and values and can get it to work for single line but not multiple. eg.

Select 'test1' as [@name], 'text1'
     FOR XML PATH('Property'), ROOT('Root')

Works giving

<Root>
  <Property name="test1">text1</Property>
</Root>

but 

Select  'test1' as [@name], 'text1'
       ,'test2' as [@name], 'text2'
       ,'test3' as [@name], 'text3'
       ,'test4' as [@name], 'text4'
     FOR XML PATH('Property'), ROOT('Root')

Fails with

Attribute-centric column '@name' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Cant find how to get the format I require above.

Upvotes: 2

Views: 5465

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

The element name used i path is repeated for each row returned. If you want to repeat the same element name for one row you need to specify the element name in the column alias and you need to separate the different elements with a empty column value. 

select 'test1' as [Property/@name],
       'text1' as Property,
       null,
       'test2' as [Property/@name],
       'text2' as Property,
       null,
       'test3' as [Property/@name],
       'text3' as Property,
       null,
       'test4' as [Property/@name],
       'text4' as Property
for xml path(''), root('Root');

Upvotes: 4

GarethD
GarethD

Reputation: 69819

Repeated notes should be rows not columns, so the following would build the required XML:

SELECT  t.[@name], t.[text()]
FROM    (VALUES
            ('Test1', 'Text1'),
            ('Test2', 'Text2'),
            ('Test3', 'Text3'),
            ('Test4', 'Text4')
        ) AS t ([@name], [text()])
FOR XML PATH('Property'), ROOT('Root')

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

You can achieve your desired output using following query:

select  T.Name as 'Property/@name', T.[Text] as 'Property'
from 
    (
        select 'test1' as Name, 'text1' as [Text]
        union all 
        select 'test2', 'text2'
        union all
        select 'test3', 'text3'
        union all
        select 'test4', 'text4'
    ) as T
for xml path(''), root('Root')

Upvotes: 1

Related Questions