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,

Gottfried Lesigang · Accepted Answer · 2018-03-24 20:43:51Z

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...

answered Mar 24, 2018 at 20:43

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Comments

thegunner · Accepted Answer · 2018-03-23 21:45:58Z

1

Ok think I got it...

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

answered Mar 23, 2018 at 21:45

thegunner

7,24334 gold badges98 silver badges145 bronze badges

Collectives™ on Stack Overflow

Construct XML with SQL in required format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related