ms sql query multiple table colums as identical tag

Question

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!

Answer 1

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

Answer 2

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.