XML issue.

[rhaazy]

using ms sql 2000

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

I get an error unless I remove the encoding attribute(<?xml
version="1.0" encoding="utf-8" ?>) from my XML document prior to
running my stored procedure. So I need a way to strip this out via my
C# code that prepares the output.

Thanks.

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

using ms sql 2000

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

I get an error unless I remove the encoding attribute(<?xml
version="1.0" encoding="utf-8" ?>) from my XML document prior to
running my stored procedure. So I need a way to strip this out via my
C# code that prepares the output.

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

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 =
string.indexof( <?xml> > version="1.0" encoding="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.

Erland Sommarskog wrote:

rhaazy (rh****@gmail.com) writes:

using ms sql 2000

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

I get an error unless I remove the encoding attribute(<?xml
version="1.0" encoding="utf-8" ?>) from my XML document prior to
running my stored procedure. So I need a way to strip this out via my
C# code that prepares the output.

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

I can not pass it as a varchar because the max is 8000 i believe, and
my xml file is too large for this.
OK, use the text data type then. The important issue is that you should
not pass it as Unicode, that is ntext/nvarchar.
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 = string.indexof( <?xml> >
version="1.0" encoding="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.

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

Erland Sommarskog wrote:

rhaazy (rh****@gmail.com) writes: OK, use the text data type then. The important issue is that you should
not pass it as Unicode, that is ntext/nvarchar.

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?

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

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

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?

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

thanks for all your help, the problem is no more.
Erland Sommarskog wrote:

rhaazy (rh****@gmail.com) writes:

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

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?

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx