XML parsing error: Switch from current encoding to specified encoding not supported.

I am using the XML capabilities of SQL Server 8 and am running the following:

-----------------------------------------------------------------------------
/** Begin Script for reproducing the problem **/

DECLARE @i_xml nVarChar(4000)

SELECT @i_xml = '<?xml version="1.0" encoding="utf-8" ?>
<TermRelations></TermRelations>'

declare @hdoc int

exec sp_xml_preparedocument @hdoc output,@i_xml

exec sp_xml_removedocument @hdoc
-----------------------------------------------------------------------------

I get the following response from Sql Server:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 10
XML parsing error: Switch from current encoding to specified encoding not supported.


Now,

When I change @i_xml to the following:
DECLARE @i_xml VarChar(4000)

I do not get the error... What is going on here?

I will need to support arabic languages with this sproc, hence the use of nVarChar. Can anyone provide guidance?
jkelly061597Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkelly061597Author Commented:
Alternatively, I can remove the '<?xml version="1.0" encoding="utf-8" ?>' from the XML and it works fine as well.
0
dunglaCommented:
jkelly,

VARCHAR is not for unicode charset (utf-8), if you want to parse the encoding utf-8 from XML to SQL server, use NVARCHAR because NVARCHAR is for unicode. That's all
0
jkelly061597Author Commented:
That is actually the problem. When I use the nVarChar I get the error. When I use varchar I do not get the error.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

vascovCommented:
Why are you specifying the enconding in the first place ? do you really need it ?
the encoding says something about how the data is supposed to be encoded, and changing from varchar to nvarchar definetely changes the encoding which is what the xml parser is complaining.
For arabic stuff just use nvarchar and do not specify any encoding.

hth

Vasco
0
jkelly061597Author Commented:
The XML I am reading comes from another system, with the <xml> encoding string.

The actual SPROC uses an nText parameter to accept the xml, this demo uses the varchar/nvarchar due to availability of local variable types.
0
Anthony PerkinsCommented:
As has been pointed out:

If encoding = 'utf-8'
    use varchar
Else (if encoding = 'utf-16')
    use nvarchar
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
And the same applies to Text and nText.

I suspect you will have to come up with either two stored procedures or use two parameters (one text and the other ntext) and a flag to indicate encoding.  You could also change the encoding from utf-8 to utf-16, prior to passing to the stored procedure.
0
jkelly061597Author Commented:
Thanks everyone,

I thought utf8 & 16 should both be stored in n*.
0
Anthony PerkinsCommented:
>>I thought utf8 & 16 should both be stored in n*.<<
But then life would be too easy <g>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.