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 SELECT...FOR XML in SQL 2000

Hi,
I have a problem with a SELECT..FOR XML Statement. I would like to do
something like:
DECLARE @.DeletedXml VARCHAR(8000)
SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id = @.Id
FOR XML AUTO, ELEMENTS)
But I get this error when I try to run: Incorrect syntax near 'XML. I have
then consulted the manual, and I can see to my big surprise that this is not
supported. Is there any way I can solve this? I want to save the @.DeletedXml
variable in a table.
Thanks
HenrikYou'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
Server 2000, you'll have to save it as native SQL datatypes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem with a SELECT..FOR XML Statement. I would like to do
> something like:
> DECLARE @.DeletedXml VARCHAR(8000)
> SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id =
> @.Id FOR XML AUTO, ELEMENTS)
> But I get this error when I try to run: Incorrect syntax near 'XML. I have
> then consulted the manual, and I can see to my big surprise that this is
> not supported. Is there any way I can solve this? I want to save the
> @.DeletedXml variable in a table.
> Thanks
> Henrik
>|||it's a bit messy, but you can
1. Put the FOR XML command as a stoerd proc
2. In .NET, execute the stored proc w/ the SqlXmlCommand object
3. Store the results of the stored proc in a stream -- Dim strm As
IO.Stream = xmlCommand.ExecuteStream()
4. Write the plain-text contents of the stream to the Database|||Hi Tom,
How can I save it as native SQL types?
Thanks Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> You'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
> Server 2000, you'll have to save it as native SQL datatypes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
>|||Thank you very much for your reply. You are right this is a bit messy :-)
I unfortunately can't do it in a stored procedure because I am using the
deleted and inserted tables.
And I would like if possible to do it all in SQL.
<scottstein@.gmail.com> wrote in message
news:1134406828.872432.69030@.g14g2000cwa.googlegroups.com...
> it's a bit messy, but you can
> 1. Put the FOR XML command as a stoerd proc
> 2. In .NET, execute the stored proc w/ the SqlXmlCommand object
> 3. Store the results of the stored proc in a stream -- Dim strm As
> IO.Stream = xmlCommand.ExecuteStream()
> 4. Write the plain-text contents of the stream to the Database
>|||You'll have to save the columns of the inserted/deleted tables to the
columns of another table or you can concatenate the values into a string and
save that - as long as the total string length is <= 8000 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> How can I save it as native SQL types?
> Thanks Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>|||Hi Tom,
Again thanks.
Ok, that is of course an idea, to skip the FOR XML attribute and generate
the XML myself. I guess that is what you mean? The 8k barrier is no problem.
Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
> You'll have to save the columns of the inserted/deleted tables to the
> columns of another table or you can concatenate the values into a string
> and save that - as long as the total string length is <= 8000 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Yeah. basically, do an INSERT SELECT * FROM deleted. When you go to SQL
2005, you can then store it as XML.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%236J6x$1$FHA.264@.tk2msftngp13.phx.gbl...
> Hi Tom,
> Again thanks.
> Ok, that is of course an idea, to skip the FOR XML attribute and generate
> the XML myself. I guess that is what you mean? The 8k barrier is no
> problem.
> Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
>