CODEWITHSUNDEEP
xmlsql-server-2008for-xml
Snowy
Snowy

Reputation: 6122

SQL Server FOR XML Enclosing Element?

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.

Upvotes: 3

Views: 2689

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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>

Upvotes: 4

jfollas
jfollas

Reputation: 1235

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

Upvotes: 4

Related Questions