2

I'm trying to use FOR XML to simulate an OData feed. One of the requirements I have is that somewhere I need an XML element which is constant for all records; I was planning to use the XMLTEXT directive for this.

My XML needs to look like this; for every record in the database,

<entry>
    <id>1</id>
    <test type="dummy" />
    <!-- some more nested elements -->
</entry>

When I use the following query, the <test> element appears before the <id> element, even though the declaration appears after it:

CREATE TABLE #temp (id int);
INSERT INTO #temp VALUES (1), (2);

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!test!XMLTEXT]
  -- other columns
  FROM #temp
  FOR XML EXPLICIT;

DROP TABLE #temp;

This gives the following results (fiddle):

<entry>
    <test type="dummy"/>
    <id>1</id>
</entry>

Am I doing something wrong here, or is this a limitation of XMLTEXT? A workaround might be to treat it as a regular nested element, but would that work even though I have more nested elements further down in the <entry> element?

2

After some more fiddling, I found out that there is an XML directive which does the job (if I omit the attribute name, test):

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!!XML]
  FROM #temp
  FOR XML EXPLICIT;

This produces the results I want:

<entry><id>1</id><test type="dummy" /></entry>

(demo)

This directive is mentioned briefly here but I couldn't find more documentation or any examples.

1

If you for some reason does not need for xml explicit you could use for xml path instead which I for one think is a bit easier to handle.

You could cast the string literal to XML

select T.id,
       cast('<test type="dummy" />' as xml)
from #temp as T
for xml path('entry');

Or you could set it up as a value

select T.id,
       'dummy' as [test/@type]
from #temp as T
for xml path('entry');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.