Structuring XML using FOR XML

Question

I am trying to return XML using FOR XML from a SQL query, and I'm almost there, except it seems like I can't get it to the exact format I need it.

Here is the statement I constructed so far:

SELECT TOP 1 
  ID AS '@id'
  ,1 AS '@version'
  ,'en' AS '@lang'
  ,'Joe Smith' AS 'field/Name'
  ,'[email protected]' AS 'field/Email' 
FROM Table
FOR XML PATH ('add')

The XML format it returns:

<add id="123" version="1" lang="en">
  <field>
    <Name>Joe Smith</Name>
    <Email>[email protected]</Email>
  </field>
</add>

How I need it to return:

<add id="123" version="1" lang="en">
  <field name="Name">Joe Smith</field>
  <field name="Email">[email protected]</field>
</add>

How do I do this, so far that's the furthest I got with the documentation I found online.. Please help.

Answer 1

1)

SELECT TOP 1 
  ID AS '@id'
  ,1 AS '@version'
  ,'en' AS '@lang'
  ,(
    SELECT  x.Attribute AS  '@name',
            x.Value     AS  'text()'
    FROM    (VALUES (N'Name', N'Joe Smith'), (N'Email', N'[email protected]')) x(Attribute,Value)
    FOR XML PATH('field'), TYPE
  )
FROM (SELECT 1 ID) AS Table1
FOR XML PATH ('add')

2) Second solution uses a template and variables. I propose this solution because i saw TOP 1 (maximum one row). First you should transfer the values from that row into variables (SELECT @Variable = Column, ... FROM Table). You have greater flexibility but the performance could be affected (note: I didn't do any tests).

DECLARE 
    @ID INT = 1,
    @Version INT = 1,
    @Lang NVARCHAR(10) = N'en',
    @Name NVARCHAR(50) = N'Joe Smith',
    @Email NVARCHAR(100) = N'[email protected]'

DECLARE @x XML = N'';
SELECT @x.query(N'
<add id="{sql:variable("@ID")}" version="{sql:variable("@Version")}" lang="{sql:variable("@Lang")}">
  <field name="Name">{sql:variable("@Name")}</field>
  <field name="Email">{sql:variable("@Email")}</field>
</add>
');