sgmunson (12/12/2013)
I'll assume your query produces a single record with the XML data type, and as the UNION ALL expects data types to match, and that my idea assumed a different scenario, this won't quite work as written, however..You could use your query as a subquery as follows:
SELECT '<?xml version="1.0" encoding="iso8859-1"?>' +
(
SELECT doc.col1, doc.col2, doc.col3
FROM TestTable AS doc
FOR XML PATH ('doc')
)
Let me know if that works for you...
That'll give you a character string that consists of well-formed XML - you have to CAST the whole thing back to XML if you want typed output:
SELECT CAST((SELECT '<?xml version="1.0" encoding="iso8859-1"?>' +
(
SELECT doc.col1, doc.col2, doc.col3
FROM TestTable AS doc
FOR XML PATH ('doc')
)) AS XML)
Also, if you specify an encoding like this but the result of the inner SELECT . . . FOR XML contains characters that are invalid for that encoding, you may get an error that SQL Server was unable to switch the encoding.
Jason Wolfkill