XML issue. -- sql server (alternate)

Erland Sommarskog
5/31/2006 9:47:48 PM

rhaazy (rhaazy@gmail.com) writes:
[quoted text, click to view]

I'm not really sure what the question is. If you want C# assistence, this
is not the best place.

Then again, OPENXML is perfectly able to handle UTF-8, but you must of
course pass the XML string as UTF-8. Specifically, you need to pass it
as a varchar value.

Try this example (which is posted in Latin-1, and should be handled as
Latin-1):

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc = '<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Customers CustomerID="VINET" ContactName=""RÃ¤ksmÃ¶rgÃ¥is">
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

rhaazy
6/1/2006 6:49:22 AM

I can not pass it as a varchar because the max is 8000 i believe, and
my xml file is too large for this. My solution should be as simple as
I have my xml stored into a string prior to it being sent to my stored
procedure, I should just be able to do something like find the int a =3D
string.indexof( <?xml> > version=3D"1.0" encoding=3D"utf-8" ?>) then find
the length of it then delete it from the string. but i dont know what
to do after finding the index.

[quoted text, click to view]

Erland Sommarskog
6/1/2006 9:10:54 PM

rhaazy (rhaazy@gmail.com) writes:
[quoted text, click to view]

OK, use the text data type then. The important issue is that you should
not pass it as Unicode, that is ntext/nvarchar.

[quoted text, click to view]

As for that question, a C# is a better place.

But as I tried to demonstrate with my repro, there should not be any
need for this.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

rhaazy
6/2/2006 6:52:35 AM

[quoted text, click to view]

Will this cause any other problems with my XML, are there any
characters that aren't included in the TEXT type that are in NTEXT?

rhaazy
6/2/2006 11:11:52 AM

thanks for all your help, the problem is no more.
[quoted text, click to view]

Erland Sommarskog
6/2/2006 2:34:16 PM

rhaazy (rhaazy@gmail.com) writes:
[quoted text, click to view]

Yes and no. Text is 8-bit only, thus normally you have access to one
code page. However, you are passing UTF-8, so you have access to the
same characters as with ntext.

Note, though, that the only context where SQL Server understands UTF-8 is
an XML document which has this encoding. You can store UTF-8 data in
text or varchar columns, but outside XML operations, SQL server will
treat the data as eight bit text, meaning to that sorting will not be
as desired, len() may return an incorrect result etc.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : XML issue.