I'm using the Biztalk Adapter for SQL Server to read data in and process it 
with an AIC.  Some of the records have invalid XML characters that look like
 a square box in SQL.  SQL returns the data properly with its FOR XML clause
, but Biztalk errors out wh
en trying to load it.  When viewed in IE with Biztalk's default UTF-16 (or w
ith UTF-8) encoding, it throws an error.  However, when I change the encodin
g to ISO-8859-1, IE opens it up correctly.  Is there a way to change the enc
oding for the document defi
nition to ISO-8859-1 to make the data come through?  Is this a bad idea?  Is
 there a better way to handle this?

Thanks for your help.
Tom

[ Post a follow-up to this message ]

I forgot to mention...Biztalk 2002

"TomW" wrote:

> I'm using the Biztalk Adapter for SQL Server to read data in and process it with a
n AIC.  Some of the records have invalid XML characters that look like a square box 
in SQL.  SQL returns the data properly with its FOR XML clause, but Biztalk errors o
ut
when trying to load it.  When viewed in IE with Biztalk's default UTF-16 (or
 with UTF-8) encoding, it throws an error.  However, when I change the encod
ing to ISO-8859-1, IE opens it up correctly.  Is there a way to change the e
ncoding for the document de
finition to ISO-8859-1 to make the data come through?  Is this a bad idea?  Is there a bette
r way to handle this?
>
> Thanks for your help.
> Tom

[ Post a follow-up to this message ]

Hi Tom,

I think this is caused by some extended characters (128-255) that are
misinterpreted as UTF-8  lead byte while loaded into DOM (DOM defaults to
UTF-8 when there are neither byte-order-mark and nor encoding declaration
in the Xml payload).

Unfortunately, there is very little that BizTalk Server proper can do to
help you here because this is how DOM works. To overcome this issue, I
think the best way is, like you suggested, to somehow add the encoding
declaration. One place that you can do this is in the custom pre-processor.

One potential alternative is to have SQL XML converts data to UTF-16 before
submitting to BizTalk (I don't know if SQL XML provides this functionality
or not). Another catch of this approach is that the UTF-16 payload cannot
have the byte-order-mark (0xFFFE) - the idea is to make it look like it was
submitted via BSTR.

HTH,

-Bill


--------------------[vbcol=seagreen] 
process it with an AIC.  Some of the records have invalid XML characters
that look like a square box in SQL.  SQL returns the data properly with its
FOR XML clause, but Biztalk errors out when trying to load it.  When viewed
in IE with Biztalk's default UTF-16 (or with UTF-8) encoding, it throws an
error.  However, when I change the encoding to ISO-8859-1, IE opens it up
correctly.  Is there a way to change the encoding for the document
definition to ISO-8859-1 to make the data come through?  Is this a bad
idea?  Is there a better way to handle this?[vbcol=seagreen] 

[ Post a follow-up to this message ]

Thanks for your help Bill!

"Bill Lo [MSFT]" wrote:

> Hi Tom,
>
> I think this is caused by some extended characters (128-255) that are
> misinterpreted as UTF-8  lead byte while loaded into DOM (DOM defaults to
> UTF-8 when there are neither byte-order-mark and nor encoding declaration
> in the Xml payload).
>
> Unfortunately, there is very little that BizTalk Server proper can do to
> help you here because this is how DOM works. To overcome this issue, I
> think the best way is, like you suggested, to somehow add the encoding
> declaration. One place that you can do this is in the custom pre-processor
.
>
> One potential alternative is to have SQL XML converts data to UTF-16 befor
e
> submitting to BizTalk (I don't know if SQL XML provides this functionality
> or not). Another catch of this approach is that the UTF-16 payload cannot
> have the byte-order-mark (0xFFFE) - the idea is to make it look like it wa
s
> submitted via BSTR.
>
> HTH,
>
> -Bill
>
>
> -------------------- 
> process it with an AIC.  Some of the records have invalid XML characters
> that look like a square box in SQL.  SQL returns the data properly with it
s
> FOR XML clause, but Biztalk errors out when trying to load it.  When viewe
d
> in IE with Biztalk's default UTF-16 (or with UTF-8) encoding, it throws an
> error.  However, when I change the encoding to ISO-8859-1, IE opens it up
> correctly.  Is there a way to change the encoding for the document
> definition to ISO-8859-1 to make the data come through?  Is this a bad
> idea?  Is there a better way to handle this? 
>
>

[ Post a follow-up to this message ]