Problem with sp_xml_preparedocument and ntext

Hi
I am trying to read by means of sp_xml_preparedocument a document XML stored
in a variable ntext, but this gives me the following error:
XML parsing error: Switch from current encoding to specified encoding not
supported.
Example XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
It is possible to do compatible unicode with xml with encoding:
sp_xml_preparedocument + ntext + encoding
Thank
Cristiánntext requires the encoding to be UCS-2 or UTF-16. You need to do the
conversion on the mid-tier before sending it to sp_xml_preparedocument.
Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
server code page that implies ISO-8859-1 encoding.
Best regards
Michael
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML
> stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristin
>|||Thanks Michael,
Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
accept UTF-16 but yes UTF-8, ?You Know Why?
XML --> UTF-16 '
Cristián
"Michael Rys [MSFT]" wrote:

> ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
> conversion on the mid-tier before sending it to sp_xml_preparedocument.
> Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
> server code page that implies ISO-8859-1 encoding.
> Best regards
> Michael
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
>
>|||For example:
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
Error...
but
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
OK
?
> Thanks Michael,
> Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does no
t
> accept UTF-16 but yes UTF-8, ?You Know Why?
> XML --> UTF-16 '
> Cristián
> "Michael Rys [MSFT]" wrote:
>|||Try:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
> For example:
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> Error...
> but
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> OK
> ?
>
>|||Hi Roger.
that work, but not thist:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
why? N-> unicode and UTF-8 idem or not?
"Roger Wolter[MSFT]" wrote:

> Try:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
>|||other example that work:
declare @.XmlInfo xml,
@.Xml nvarchar(max)
set @.Xml= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
set @.XmlInfo = @.Xml
select @.XmlInfo
--nvarchar --> XML
"sqlextreme" wrote:

> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML stor
ed
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristián
>|||This works because character data is expected to be double-byte
declare @.XmlInfo xml
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This works because character data is expected to be single-byte.
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
In other words, if the encoding is UTF-8, the string holding it has to be
varchar ('<xml...>'); and if the encoding is UTF-16, then the string holding
it has to be nvarchar (N'<xml...>')
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
>|||The XML parser doesn't like being lied to. If you say it's utf-8 data you
need to pass it 8 bit data. If you say it's utf-16 you need to give it 16
bit data. In your example you prefix the string with an N which means the
string is Unicode so the parser parses Unicode data. When it runs into your
declaration that says it's utf-8 it is already parsing utf-16 so it errors
out because its is doing the wrong thing.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
>

Problem with sp_xml_preparedocument and ntext

Hi
I am trying to read by means of sp_xml_preparedocument a document XML stored
in a variable ntext, but this gives me the following error:
XML parsing error: Switch from current encoding to specified encoding not
supported.
Example XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
It is possible to do compatible unicode with xml with encoding:
sp_xml_preparedocument + ntext + encoding
Thank
Cristián
ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
conversion on the mid-tier before sending it to sp_xml_preparedocument.
Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
server code page that implies ISO-8859-1 encoding.
Best regards
Michael
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML
> stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristin
>
|||Thanks Michael,
Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
accept UTF-16 but yes UTF-8, ?You Know Why?
XML --> UTF-16 ?
Cristián
"Michael Rys [MSFT]" wrote:

> ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
> conversion on the mid-tier before sending it to sp_xml_preparedocument.
> Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
> server code page that implies ISO-8859-1 encoding.
> Best regards
> Michael
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
>
>
|||For example:
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
Error...
but
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
OK
?
[vbcol=seagreen]
> Thanks Michael,
> Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
> accept UTF-16 but yes UTF-8, ?You Know Why?
> XML --> UTF-16 ?
> Cristián
> "Michael Rys [MSFT]" wrote:
|||Try:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...[vbcol=seagreen]
> For example:
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> Error...
> but
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> OK
> ?
>
>
|||Hi Roger.
that work, but not thist:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
why? N-> unicode and UTF-8 idem or not?
"Roger Wolter[MSFT]" wrote:

> Try:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
>
|||other example that work:
declare @.XmlInfo xml,
@.Xml nvarchar(max)
set @.Xml= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
set @.XmlInfo = @.Xml
select @.XmlInfo
--nvarchar --> XML
"sqlextreme" wrote:

> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristián
>
|||This works because character data is expected to be double-byte
declare @.XmlInfo xml
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This works because character data is expected to be single-byte.
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
In other words, if the encoding is UTF-8, the string holding it has to be
varchar ('<xml...>'); and if the encoding is UTF-16, then the string holding
it has to be nvarchar (N'<xml...>')
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
|||The XML parser doesn't like being lied to. If you say it's utf-8 data you
need to pass it 8 bit data. If you say it's utf-16 you need to give it 16
bit data. In your example you prefix the string with an N which means the
string is Unicode so the parser parses Unicode data. When it runs into your
declaration that says it's utf-8 it is already parsing utf-16 so it errors
out because its is doing the wrong thing.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:

Problem with sp_xml_preparedocument

Hi All,

Follwing are the 2 code snippets of XOPEN

1.
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH (s varchar(100) '@.s')

2.

create table newtemp
(
s varchar(100)
)
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH newtemp

Code snippet 2 works but 1 doesnot.

Can some one me the reason why ?

Thanks & Regards

Nitesh

Try changing

WITH (s varchar(100) '@.s')

to

WITH (s varchar(100) 's')

|||

Thanks a lot !!!!

It worked out.

Problem with procedure call style

I try to execute code:
...
CallableStatement prep = conn.prepareCall("{call sp_xml_preparedocument
(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
(?)}");
rem.setInt(1, prep.getInt(1));
rem.executeUpdate();
...
Java prints Exception Could not find prepared statement with handle 1.
Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
How can I fix it?
Thank You!
| From: "Yuri Shustrov" <yuri_shustrov@.mail.ru>
| Subject: Problem with procedure call style
| Date: Fri, 10 Dec 2004 16:03:46 +0300
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OBfhHir3EHA.2404@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: ns.escort-center.ru 212.176.17.195
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP14
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6541
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I try to execute code:
| ...
| CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument
| (?, ?)}");
| prep.registerOutParameter(1, java.sql.Types.INTEGER);
| prep.setString(2, "<root/>");
| prep.executeUpdate();
| System.out.println(prep.getInt(1));
| CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
| (?)}");
| rem.setInt(1, prep.getInt(1));
| rem.executeUpdate();
| ...
| Java prints Exception Could not find prepared statement with handle 1.
| Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
| How can I fix it?
| Thank You!
|
|
|
Hello Yuri,
The SQL Server 2000 Books Online topic "sp_xml_preparedocument" states the
following:
"sp_xml_preparedocument returns a handle that can be used to access the
newly created internal representation of the XML document. This handle is
valid for the duration of the connection to Microsoft SQL Server 2000,
until the connection is reset, or until the handle is invalidated by
executing sp_xml_removedocument."
Your code shows two CallableStatements being prepared and executed on the
same connection variable "conn" without closing the first
CallableStatement. If you are using "SelectMethod=direct", then this will
result in a new cloned connection. Since the handle is only valid on the
first connection, the call to sp_xml_removedocument will fail. You can
resolve this by specifying "SelectMethod=cursor" in your connection string.
Otherwise, you can still use "SelectMethod=direct" and simply modify your
code so that the first CallableStatement is closed before the second
CallableStatement is prepared. This will maintain the same underlying
connection, and so the document handle will still be valid:
CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
int handle = prep.getInt(1);
prep.close();
prep = null;
CallableStatement rem = conn.prepareCall("{call
sp_xml_removedocument(?)}");
rem.setInt(1, handle);
rem.executeUpdate();
rem.close();
rem = null;
You can verify this behavior using SQL Profiler. In the failing scenario,
you will observe two different SPIDs (two different connections).
Hope that helps!
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.