CODEWITHSUNDEEP
sqlxmlt-sqlsql-server-2008-r2
mdisibio
mdisibio

Reputation: 3520

Shape a FOR XML query against an xml column to include attribute in root

Given a table with an xml column:

/*
CREATE TABLE #test (id int, content xml);
INSERT INTO #test VALUES (1, N'<i>abc</i>'),(1, N'<i>def</i>');
*/

I can create an xml document for downstream deserialization easily:

  SELECT content AS [*]
    FROM #test T
 FOR XML PATH(''), ROOT('root')

yielding:

/*
<root>
  <i>abc</i>
  <i>def</i>
</root>
*/

However, I would like to add an attribute to 'root', with the ideal output of:

/*
<root tag="42">
  <i>abc</i>
  <i>def</i>
</root>
*/

So far, all I can manage to accomplish this is adding an additional level to the xml structure:

  SELECT 42 AS [@tag],
         (SELECT content AS [*]  FROM #test T FOR XML PATH(''), TYPE) AS [*]
 FOR XML PATH('extra'), ROOT('root')

yielding:

/*
 <root>
  <extra tag="42">
    <i>abc</i>
    <i>def</i>
  </extra>
</root>
*/

because

  SELECT 42 AS [@tag],
         (SELECT content AS [*]  FROM #test T FOR XML PATH(''), TYPE) AS [*]
 FOR XML PATH(''), ROOT('root')

is not allowable syntax. Are there any suggestions for adding an attribute to the root node without adding an extra level?

Upvotes: 0

Views: 110

Answers (1)

mdisibio
mdisibio

Reputation: 3520

Well, answer seems super simple now, but took several iterations to figure out:

  SELECT 42 AS [@tag],
         (SELECT content AS [*]  FROM #test T FOR XML PATH(''), TYPE) AS [*]
 FOR XML PATH('root')

I didn't realize the FOR XML syntax could leave out both the ROOT and TYPE specifiers, especially since doing so would have only yielded a set of fragments for the original query.

Upvotes: 1

Related Questions