SQL Server FOR XML - Basic query

Question

I have been given an XML document that I want to generate via a SQL script, I've not done something like this and haven't been able to find any examples that can lead me to being able to generate the final XML I need (and I'm not sure which of the possible methods available if one is better suited to what I need - EXPLICIT or PATH or if its even possible). I'm hoping somebody with some experience in generating XML from SQL will be able to point me in the right direction (or tell me what I'm trying to do is impossible and that I need to do it with sub-queries).

The scenario is I'm returning product details from a single table (I would prefer to not have to do sub-queries for each of the values I need).

The xml I'm hoping to be able to generate looks like (I have no control over this format):

<records>
    <record>
        <fields>
            <field name="id">
                <values>
                    <value>666111</value>
                </values>
            </field>
            <field name="name">
                <values>
                    <value>
                        <![CDATA[My Product Title]]>
                    </value>
                </values>
            </field>
        </fields>
    </record>
    <record>
        ...
    </record>
</records>

The first method I've looked at is using FOR XML PATH

SELECT TOP 2 
    'id' AS "@name",
    p.product_id as [value], 
    p.title
FROM products p 
ORDER BY p.product_id DESC
FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS;

and this gives me the XML:

<fields>
  <field name="id">
    <value>20624</value>
    <title>test154</title>
  </field>
  <field name="id">
    <value>20623</value>
    <title>test153</title>
  </field>
</fields>

Which gives me the '' that I need, but I can't then specify the layout I need for the next elements.

I also looked into FOR XML EXPLICIT

SELECT TOP 2
    1 AS Tag, NULL AS Parent,
    p.product_id AS [record!1!product_id!ELEMENT],
    NULL AS [values!2!value!ELEMENT]
FROM products p 
UNION ALL
SELECT TOP 2 
    2, 1,
    p.product_id,
    p.title
FROM products p 
ORDER BY [record!1!product_id!ELEMENT] DESC
FOR XML EXPLICIT;

Which gave me the following XML:

<record>
  <product_id>20624</product_id>
  <values>
    <value>test154</value>
  </values>
</record>
<record>
  <product_id>20623</product_id>
  <values>
    <value>test153</value>
  </values>
</record>

I'm a bit lost in being able to build up the request or get something that is along the right lines (and I think I'm trying to do too much in a single lookup and that is the cause of my problem). Any help is appreciated - even if its pointing me at a good guide (the only ones I've found have been very poor when it comes to examples - they don't show the subtleties of how you can build/change them)

Answer 1

This is the query you might be looking for

The ,'' in the middle is a trick which allows you to create several elements with the same name one below the other...

DECLARE @tbl TABLE(id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (666111,'My Product Title 111')
,(666222,'My Product Title 222');

SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,name AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')

The result

<records>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666111</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 111</value>
        </values>
      </field>
    </fields>
  </record>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666222</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 222</value>
        </values>
      </field>
    </fields>
  </record>
</records>

UPDATE: As far as I know there is no clean way to add CDATA-sections

For some reasons people at Microsoft think, that CDATA sections are not necessary. Well, they aren't but still sometimes they are demanded...

The only clean way to add CDATA sections was to use FOR XML EXPLICIT. Another workaround was to put something like '|' + name + '#' (use two characters wich will never occur in your actual data.

Then you can cast the result to NVARCHAR(MAX), replace these characters on string base.

This would return your XML as string

SELECT 
REPLACE(REPLACE(CAST(
(
SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,'|' + name + '#' AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>')

At the moment you cast this back to XML the CDATA is gone :-(

Answer 2

The final SQL I used is:

SELECT TOP 2
(
    SELECT  
        (SELECT 'id' AS [field/@id],
        product_id [field/values/value]
        FOR XML PATH(''), TYPE),
        (SELECT 'title' AS [field/@id],
        title [field/values/value]
        FOR XML PATH(''), TYPE)
    FOR XML PATH('fields'), TYPE
)
FROM products 
FOR XML PATH('record'), ROOT('records')

As this allows me to manipulate the output a little easier.

Thank you to both @xdd and especially @Shnugo for your answers! The end solution is based on @Shnugo's suggestion, just with avoiding the trick of putting extra blank rows in.

Answer 3

something like that

    declare @t table (id varchar(10))

    insert into @t values ('1')
    insert into @t values ('2')

    select (
            select 
                    t.id 'fields/field/@id'
                    , t.id 'fields/field/name'
                from @t t
                for xml path(''), type
        ) 'records/record'
        for xml path('')