0

I am using SQL Server Express 2017

My Table looks like this

+-------------+
| ID | Name   |
+-------------+
| 1  | Bert   |
| 2  | Agnes  |
| 3  | Diane  |
+-------------+

I'm looking for this kind of query result:

<MyRootElement>
  <MyTag ID='1' Name='Bert' />
  <MyTag ID='2' Name='Agnes' />
  <MyTag ID='3' Name='Diane' />
</MyRootElement

Can anyone help me, please?

Thanks in advance!

0
SELECT Id, Name
FROM MyTable 
FOR XML RAW ('MyTag'), ROOT ('MyRootElement');
3

You need to use FOR XML clause at the end. You can specify each row's node with PATH and the root with ROOT. Also the having a column alias with @ at the start with move the value to an attribute.

;WITH YourTable AS 
(
    SELECT
        V.ID,
        V.Name
    FROM
        (VALUES 
         (1, 'Bert'),
         (2, 'Agnes'),
         (3, 'Diane')
         ) V(ID, Name)
)
SELECT
    [@ID] = T.ID,
    T.Name AS '@Name'
FROM
    YourTable AS T
FOR XML
    PATH('MyTag'),
    ROOT('MyRootElement')

Result:

<MyRootElement>
  <MyTag ID="1" Name="Bert" />
  <MyTag ID="2" Name="Agnes" />
  <MyTag ID="3" Name="Diane" />
</MyRootElement>

You can find all the XML options on the docs. This particular PATH mode is explained here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.