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.

This does it for one but the problem is cannot use it for two as cannot repeat a column name ,'fieldName' as 'field/@name', [table].[colX] as 'field' — paparazzo
– paparazzo, Commented Dec 30, 2013 at 20:59

Bogdan Sahlean · Accepted Answer · 2013-12-30 21:58:28Z

3

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>
');

edited Dec 30, 2013 at 21:58

answered Dec 30, 2013 at 17:26

Bogdan Sahlean

19.3k3 gold badges48 silver badges60 bronze badges

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

2 Comments

Control Freak Over a year ago

Thanks, but for your second solution, would I need to set up a cursor or something to go through each row if the result is not only 1 row? (TOP 1)

Bogdan Sahlean Over a year ago

@ZeeTee: If you have many rows (not TOP 1) then the second script isn't a solution. Use instead the first solution.

Collectives™ on Stack Overflow

Structuring XML using FOR XML

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related