CODEWITHSUNDEEP
sql-serverxml
user3535647
user3535647

Reputation: 45

How to generate xml tag for value

I wish to generate xml data in the following format - animal value must be tag name 

<pets>
  <dog>
    <name>spot</name>
  </dog>
  <cat>
    <name>tom</name>
  </cat>
</pets>

I can write some dynamic sql but if there is a better way???? 

;with cte(animal,name)
as
(
    select 'dog','spot' union
    select 'cat','tom'
)
select 
    animal
    ,name
from cte
for xml path(animal),root('pets')

Upvotes: 0

Views: 44

Answers (1)

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

Use the next code:-

SELECT ( 
SELECT 'spot' AS name
FOR
XML PATH('dog'),
TYPE
),
( SELECT 'tom' AS name
FOR
XML PATH('cat'),
TYPE
)
FOR XML PATH(''),
ROOT('Pets')
GO

Result:-

<Pets>
  <dog>
    <name>spot</name>
  </dog>
  <cat>
    <name>tom</name>
  </cat>
</Pets>

Upvotes: 1

Related Questions