Wednesday, August 10, 2005 8:16 PMHow can I create XML Schema Collection using UTF-8 encoding?
I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:
.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 38, unable to switch the encoding
I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?
Friday, August 12, 2005 8:13 PMThe XML schema is being treated as UTF-16, hence the mismatch in the encoding.
You can do one of the following:
1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server
2) Send the XML schema as a binary type from the .NET client to the server.
The server's XML parser will use UTF-8 encoding while parsing the XML schema.
A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.
Hope this helps.
Microsoft SQL Server
Wednesday, August 23, 2006 6:31 PM
I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?
Sunday, September 03, 2006 2:22 AM
Assuming you have a stored proc parameter @xsc of type varbinary(max) or type xml, you just say:
create xml schema collection sc as @xsc
how are you planning on passing the values to the database then?
Tuesday, December 19, 2006 5:08 AM
I don't know whether this has been resolved or not, but for completeness, here is an answer:
CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
Is it right?
Sunday, October 21, 2007 3:15 AM
for a 98% BLL programmer who sometimes work at the db layer... this wasn't obvious first.
Monday, August 18, 2008 10:49 AMHi,
I'm trying to figure out the optimal way to store UTF-8 XML in SQL Server 2005 as well. We're using C# 3.0 and NHibernate for our data access.
In our code we prefer to handle our XML as byte arrays, for quick transformations with XSLT. Furthermore, we'd like our types not to be strongly dependent on the SQL XML datatype, so that's why I would not like my C# properties to be of type SqlXml. Given that, I think we would have to go for option 2, passing a binary type to the server. My questions are:
(1) do I understand correctly that if I pass a byte array to the SQL Server for a column defined as XML, the server will take care of UTF-8 to UTF-16 conversion?
(2) how exactly does the server determine that the binary data is UTF-8 encoded (sorry, I'm not that far into the UTF-8 encoding theory yet)?
(3) in this scenario, how do I retrieve the XML from the server in UTF-8 encoding? Do I have to explicitly ask the server to convert from UTF-16 and if so, how?
(4) what is the expected performance hit for this conversion? In other words: is the above approach the best choice for storing UTF-8 XML in the database, or would we be better of using a different approach?
(5) is SQL Server 2008 expected to make our life easier in this area?
Monday, April 30, 2012 12:15 PM
Send the XML schema as a System.Data.SqlTypes.SqlXml type
this gives a context error in c#:
command.Parameters.Add("@data", System.Data.SqlTypes.SqlXml).Value = xmlDoc.InnerXml;