2

I'd like to add the declaration line of my xml to the beginning of my output. I insert the result into a temp table with only one column of type Xml. Without the declaration line the insert is successful but otherwise I get the error "illegal xml character".

DECLARE @x XML
DECLARE @y XML
DECLARE @result XML
DECLARE @declarationLine VARCHAR(MAX)
DECLARE @strXML VARCHAR(MAX)

SET @declarationLine = '<?xml version="1.0" encoding="utf-8"?>'
SET @result = (SELECT @x,@y FOR XML PATH('contacts'))
SET @strXML = @declarationLine + CONVERT(varchar(MAX),@result)

SELECT CONVERT(XML,@strXML) AS xmloutput
INTO [dbo].[xml_temp]

How can I add the declaration line so that it shows up in the results?

disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • 3
    The XML type will not preserve a declaration. If you want one in combination with the XML type you will need to convert the XML to an NVARCHAR and prepend it as a string when you need to. – Alex K. Feb 02 '17 at 12:27
  • 4
    (Note it also wont be utf-8 because your not using nvarchar & the internal format is ucs-2) – Alex K. Feb 02 '17 at 12:31

0 Answers0