XQuery - Need to specify the XML encoding in the first row

[thisisboni]

Hi:

I have a tsql result set stored in the dB an xml column using XQuery (FOR XML)
it is missing the encoding information <?xml version="1.0" encoding="UTF-8"?>

How can I add this to the column ?

 

[TomKane]

Hi I tried it but I didn't have much luck. I was using SQL 2005

I tried this:

declare @Table as table
(
ID int identity(1,1),
ResultSet xml
)


insert into @table
select '<Test>1</Test>' union
select '<Test>2</Test>' union
select '<Test>3</Test>' union
select '<Test>4</Test>' union
select '<Test>5</Test>' union
select '<Test>6</Test>'

update @table
set resultset = '<?xml version="1.0" encoding="UTF-8"?>' + cast(resultset as varchar(MAX))

select * from @table

The <?xml version="1.0" encoding="UTF-8"?> didn't stick - so I did a bit of reading up and I found XML data type stores data in Unicode UTF-16.

I'll send you the link to the article but it appears to me that if you're using the XML data type the encoding part doesn't "stick".

Good luck!

http://msdn.microsoft.com/en-us/library/ms345115(v=sql.90).aspx

The part I was looking at was the "Text Encoding" section.

 

[thisisboni]

Yes - that is what I did - instead of storing it as xml I am converting it to varchar(max) and adding the encoding info in the beginning.

Thanks