sql server - SQL FOR XML Path, returning multiple child elements -


i require data returned 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 code other post sql server xml path add attributes , values , can work single line not multiple. eg.

select 'test1' [@name], 'text1'      xml path('property'), root('root') 

works giving

<root>   <property name="test1">text1</property> </root> 

but

select  'test1' [@name], 'text1'        ,'test2' [@name], 'text2'        ,'test3' [@name], 'text3'        ,'test4' [@name], 'text4'      xml path('property'), root('root') 

fails with

attribute-centric column '@name' must not come after non-attribute-centric sibling in xml hierarchy in xml path.

cant find how format require above.

the element name used path repeated each row returned. if want repeat same element name 1 row need specify element name in column alias , need separate different elements empty column value.

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

Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -