ms sql query multiple table colums as identical tag 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:

&lt;MyRootElement&gt;
  &lt;MyTag ID='1' Name='Bert' /&gt;
  &lt;MyTag ID='2' Name='Agnes' /&gt;
  &lt;MyTag ID='3' Name='Diane' /&gt;
&lt;/MyRootElement


Can anyone help me, please?

Thanks in advance!
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:

&lt;MyRootElement&gt;
  &lt;MyTag ID="1" Name="Bert" /&gt;
  &lt;MyTag ID="2" Name="Agnes" /&gt;
  &lt;MyTag ID="3" Name="Diane" /&gt;
&lt;/MyRootElement&gt;


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