SQL Server 2008 - Add XML Declaration to XML Output -

i've been battling 1 few days now, i'm looking automate xml output below syntax

 select (    select convert(varchar(10),getdate(),103)    xml path('dataversion'),       type    ),    (      select conum,      coname,      convert(varchar(10),accounttodate,103) 'dla',      lafilenet    @xmloutput      xml path('company'),      type      )  xml path(''),    root('companies') 

which creates below output

<companies>   <dataversion>15/11/2010</dataversion>   <company>     <conum>111</conum>     <coname>abclmt</coname>     <dla>12/12/2010</dla>     <lafilenet>1234</lafilenet>   </company>   <company>     <conum>222</conum>     <coname>deflmt</coname>     <dla>12/12/2007</dla>     <lafilenet>5678</lafilenet>   </company> </companies> 

what i'm struggling how add xml declaration <?xml version="1.0" encoding="iso-8859-1" ?> top of output??

update 1: correct in thinking need create xml schema in sql server define xsl:output. assign output schema?

update 2: have since found these links http://forums.asp.net/t/1455808.aspx -- check out comment jian kang. http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic60022.aspx

tl;dr

concatenate this: <?xml version="1.0" encoding="windows-1252" ?> xml, converted varchar(max).

details

i agree j0n45 schema not change anything. the answer references points out:

you have add manually.

i provided example code in another answer. basically, convert xml varchar or nvarchar , concatenate xml declaration, such <?xml version="1.0" encoding="windows-1252" ?>.

however, it's important choose right encoding. sql server produces non-unicode strings according collation settings. default, governed database collation settings, can determine using sql:

select databasepropertyex('exampledatabasename', 'collation'); 

a common default collation "sql_latin1_general_cp1_ci_as", has code page of 1252. can retrieve code page sql:

select collationproperty('sql_latin1_general_cp1_ci_as', 'codepage') 'codepage'; 

for code page 1252, should use encoding name of "windows-1252". use of "iso-8859-1" inaccurate. can test using "bullet" character: •. has unicode code point value of 8226 (hex 2022). can generate character in sql reliably, regardless of collation, using code:

select nchar(8226); 

it has has code point of 149 in windows-1252 code page, if using common, default collation of "sql_latin1_general_cp1_ci_as", can produce using:

select char(149); 

however, char(149) won't bullet in collations. example, if try this:

select convert(char(1),char(149)) collate chinese_hong_kong_stroke_90_bin; 

you don't bullet @ all.

the "iso-8859-1" code page windows-28591. none of sql server collations (in 2005 anyway) use code page. can full list of code pages using:

select [name], [description], [codepage] = collationproperty([name], 'codepage') ::fn_helpcollations() order [codepage] desc; 

you can further verify "iso-8859-1" wrong choice trying use in sql itself. following sql:

select convert(xml,'<?xml version="1.0" encoding="iso-8859-1"?><test>•</test>'); 

will produce xml not contain bullet. indeed, won't produce character, because iso-8859-1 has no character defined code point 149.

sql server handles unicode strings differently. unicode strings (nvarchar), "there no need different code pages handle different sets of characters". however, sql server not use "utf-8" encoding. if try use within sql itself:

select convert(xml,n'<?xml version="1.0" encoding="utf-8"?><test>•</test>'); 

you error:

msg 9402, level 16, state 1, line 1 xml parsing: line 1, character 38, unable switch encoding

rather, sql uses "ucs-2" encoding, work:

select convert(xml,n'<?xml version="1.0" encoding="ucs-2"?><test>•</test>');