create XML Schema Collection Using UTF-8 in SQL Server 2005

Question

How 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?

Answer 1

The 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.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

Answer 2

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?

 

Thanks,

Eileen Torrens

Answer 3

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?

Best regards

Michael

Answer 4

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

Answer 5

thanks!

for a 98% BLL programmer who sometimes work at the db layer... this wasn't obvious first.

 

Answer 6

Hi,

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?

Thanks!

Pascal

Answer 7

 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;