Construct XML with SQL in required format

Question

I'm doing a select on a table only selecting two columns.

I would like to construct the following XML

        <root>
            <choice value='1'>choice 1</choice>
            <choice value='2'>choice 2</choice>
            <choice value='3'>choice 3</choice>
        </root>

I can currently get:

<root>
  <choice value="1">choice 1</choice>
</root>
<root>
   <choice value="2">choice 2</choice>
</root>
<root>
   <choice value="3">choice 3</choice>
</root>

using this SQL:

SELECT 
ID AS 'choice/@value',
DisplayName AS 'choice'
FROM tablename
WHERE [Status] = 'open'
FOR XML PATH ('root')

What do I need to change to get that format? i.e. the choice elements within one root element?

Thanks,

Answer 1

You found your answer, great (+1 from my side), just one more choice :-)

DECLARE @tbl TABLE(ID INT IDENTITY,DisplayName VARCHAR(100));
INSERT INTO @tbl VALUES('Choice 1'),('Choice 2');

SELECT ID AS [@value]
      ,DisplayName AS [*]
FROM @tbl 
FOR XML PATH('choice'),ROOT('root')

The PATH('x') gives a node to each table row, while the ROOT('y') wraps the whole output into a root element.

The code above will give back exactly the same, but is a bit closer to the way it is meant to be...