SQL Server internally represents XML in an efficient binary representation that uses UTF-16 encoding. User-provided encoding is not preserved, but is considered during the parse process.
That’s why there is no XML encoding returned in your case. Please refer to the following link, which suggests convert the XML to a string type and then add the XML declaration.
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore,
Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you
completely understand the risk before retrieving any software from the Internet.
Jian Kang
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
The XML data type (SQL Server) does not preserve processing information. So if you assign an XML document with processing information, it will be lost. If you want to return an XML document with processing information, you should return a well-formed XML
string, instead of returning an XML data type value.
faisalv
0 Points
2 Posts
Generating XML in SQL with Encoding Tag
Aug 06, 2009 07:22 AM|LINK
I just have a issue with generating XML from SQL Server. See sample below.
DECLARE @xmlsample varchar(max)
SET @xmlsample='<?xml version="1.0" encoding="iso-8859-1" ?>'
SET @xmlsample= @xmlsample + '<Stuff><name>Müller</name></Stuff>'
SELECT CONVERT(XML,@xmlsample)
The output of the query is as follows.
<Stuff>
<name>Müller</name>
</Stuff>
However I want the output to include the encoding tag as below.
<?xml version="1.0" encoding="iso-8859-1" ?>
<Stuff>
<name>Müller</name>
</Stuff>
Do you know of a way which I can do this?
rashmika
Participant
936 Points
148 Posts
Re: Generating XML in SQL with Encoding Tag
Aug 06, 2009 08:58 AM|LINK
hi
see this
http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/
http://stackoverflow.com/questions/147897/in-sql-server-can-i-insert-multiple-nodes-into-xml-from-a-table
http://beyondrelational.com/blogs/jacob/archive/2009/01/09/for-xml-path-yet-another-shaping-example-using-for-xml-path.aspx
Rashmika
If it helps u then "Mark As Answer".
faisalv
0 Points
2 Posts
Re: Generating XML in SQL with Encoding Tag
Aug 06, 2009 09:11 AM|LINK
These articles does not show you how to generate xml with version and encoding tag.
<?xml version="1.0" encoding="iso-8859-1" ?>
That is what I need my generated XML to have.
rashmika
Participant
936 Points
148 Posts
Re: Generating XML in SQL with Encoding Tag
Aug 06, 2009 10:38 AM|LINK
hi,
try this
http://www.15seconds.com/issue/001102.htm
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
http://www.dotnet247.com/247reference/msgs/3/17437.aspx
http://video.google.com/videosearch?hl=en&client=firefox-a&channel=s&rls=org.mozilla:en-US:official&q=create+xml+from+database+sql+server&um=1&ie=UTF-8&ei=LLR6SsLBKJWBtwf1ivjmAQ&sa=X&oi=video_result_group&ct=title&resnum=4#
Rashmika
If it helps u then "Mark As Answer".
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Generating XML in SQL with Encoding Tag
Aug 11, 2009 10:00 AM|LINK
Hi faisalv,
Please see the following description:
SQL Server internally represents XML in an efficient binary representation that uses UTF-16 encoding. User-provided encoding is not preserved, but is considered during the parse process.
Generating XML Instances
http://msdn.microsoft.com/en-us/library/ms190965.aspx
That’s why there is no XML encoding returned in your case. Please refer to the following link, which suggests convert the XML to a string type and then add the XML declaration.
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic60022.aspx
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
jacob_sebast...
Member
140 Points
20 Posts
Re: Generating XML in SQL with Encoding Tag
Aug 12, 2009 06:53 AM|LINK
The XML data type (SQL Server) does not preserve processing information. So if you assign an XML document with processing information, it will be lost. If you want to return an XML document with processing information, you should return a well-formed XML string, instead of returning an XML data type value.