adding attribute to the same tag in xml in sql server

Question

i have a table named 'Words' in sql server with these columns: Id, Type, Word

i want to write a query to generate a xml like this

<Words>
  <Word Id="1" Type="8">some text1</Word>
  <Word Id="2" Type="4">some text2</Word>
  <Word Id="3" Type="5">some text3</Word>
</Words>

how can i do that?

Martin Smith · Accepted Answer · 2012-01-31 14:46:50Z

3

WITH Words(Id, Type, Word) AS
(
SELECT 1, 8, 'some text1' UNION ALL
SELECT 2, 4, 'some text2' UNION ALL
SELECT 3, 5, 'some text3' 
)
SELECT Id   AS [@Id],
       Type AS [@Type],
       Word AS [text()]
FROM Words
FOR XML PATH('Word'), ROOT('Words')

answered Jan 31, 2012 at 14:46

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

adding attribute to the same tag in xml in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related