SQL Server FOR XML Enclosing Element?

Question

Using SQL Server 2008, I have a query that emits a result set using FOR XML. Right now it is a non-compliant fragment.

How can I wrap my result XML in an enclosing element and then put a simple XML declaration on top with a single schema/namespace reference to make the output compliant?

Thanks.

Answer 1

It is not possible to have the XML processing instruction in a XML datatype in SQL Server.

See Limitations of the XML Data Type

This code

declare @XML xml =  
  '<?xml version="1.0"?>
   <root>Value</root>'

select @XML

Has the output

<root>Value</root>

You can build the XML as a string with the XML processing instruction in place.

declare @XML xml = '<root>Value</root>'
declare @XMLStr nvarchar(max) = '<?xml version="1.0"?>'
  
set @XMLStr = @XMLStr + cast(@XML as nvarchar(max))

select @XMLStr

Output

--------------------------------------------------------------------------
<?xml version="1.0"?><root>Value</root>

Answer 2

Add "WITH XMLNAMESPACES" to the beginning and a ROOT() to the FOR XML clause:

WITH XMLNAMESPACES ( DEFAULT 'http://namespace_uri_here' )
SELECT * 
FROM TABLE
FOR XML AUTO, ROOT('TopLevel')