none
Error using RECEIVE with xml

    Frage

  • I have a Stored procedure that acts upon a queue with the following code:

    DECLARE @message_body as XML, @MessageTypeName as varchar(256), @ConversationHandle as UniqueIdentifier

    WAITFOR (RECEIVE TOP (1)   @MessageTypeName = message_type_name

    ,@message_body = message_body

    ,@ConversationHandle = Conversation_Handle

    FROM MyQueue

    ), TIMEOUT 5000;

    This works well for 90% of the messages in the queue, but bugs out the other 10% of the time. The SQL Log error message being:

    The activated proc '[dbo].[usp_ReadMyQueue]' running on queue 'DBName.dbo.MyQueue' output the following:  'XML parsing: line 1, character 519, illegal xml character'

    When I checked the message_body for the message causing the error, I found that the character position was represented by a character: ã

    I therefore changed the code by adding a new variable @message_body2 as varchar(max), and placing that inside the RECEIVE statement, thus:

    .....

    ,@message_body2 = message_body

    ......

    and following this I added the next line after the TIMEOUT being:

    SET @message_body = CAST(@message_body2 as XML)

    It all works perfectly well.

    My question is why would an xml error be raised in the RECEIVE statement when it is not outside of that statement?

    I did try using ,@message_body = CAST(message_body as xml) but that also errored out the same as before.

    Just being curious as to whether this should be expected behaviour, or a little-known bug.

    Thanks, 

    Mittwoch, 12. Juni 2013 20:15

Alle Antworten

  • Are you sure you're casting the message body to XML for all message types?  If so can you post an example of a message_body in binary that fails if you cast it in RECEIVE but succeeds if you cast in a subsequent statement?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Mittwoch, 12. Juni 2013 20:52
  • David,

    Thanks for the response. I have taken this a little further and need to amend my original assumption that this is directly related to the RECEIVE statement, it is not, but it is related to the casting of varbinary(max) to xml. My apologies for the error on my part. 

    The below code will demonstrate the error received. I have set up 2 varbinary examples, one in English only, the other with Portuguese, and use the same code to cast them to varchar(max) and also xml, with different results. Those with a 1 suffix refer to English and 2 to Portuguese.

    I believe we now understand the cause, but if you could confirm why the error occurs, it would be appreciated.

    If this thread needs to be moved out of Service Broker, please feel free,

    Thanks,

    Tony

    --#####################################################

    SET NOCOUNT ON

    DECLARE @Message_BodyVBm1 as varbinary(MAX) = 0x3C5461736B3E3C5365727665724E616D653E4142435C464F4F3C2F5365727665724E616D653E3C44424E616D653E466F6F4261723C2F44424E616D653E3C436F6D6D616E643E7374557064617465466F6F42617220436F6E74657874536E617073686F743D20266C743B526F6F742667743B266C743B456E7469747920456E746974794E616D653D22436861726765222044617461436F6E746578744E616D653D22747255706461746543747822204170706C69636174696F6E496E7374616E63653D223261222046756C6C546578745365617263683D223022204375727254696D655374616D703D22323031332D30362D30372031313A30313A34372E38363333303231222052756C654E616D653D22436861726765734368616E676564222667743B266C743B43757272536E617073686F742667743B266C743B726F772667743B266C743B526F774E756D6265722667743B31266C743B2F526F774E756D6265722667743B266C743B427573696E6573735479706549642667743B323536266C743B2F427573696E6573735479706549642667743B266C743B436861726765416D6F756E742667743B3130302E30303030266C743B2F436861726765416D6F756E742667743B266C743B4368617267654465736372697074696F6E2667743B416C6C6F636174696F6E2072617465266C743B2F4368617267654465736372697074696F6E2667743B266C743B436861726765466F72547970652667743B30266C743B2F436861726765466F72547970652667743B266C743B43686172676549642667743B3537333837266C743B2F43686172676549642667743B266C743B49642667743B32303133303630372D303030352D31266C743B2F49642667743B266C743B504B2667743B313230333531266C743B2F504B2667743B266C743B4E6F646556616C75652667743B32303133303630372D303030352D31266C743B2F4E6F646556616C75652667743B266C743B506172656E7449442667743B32303133303630372D30303035266C743B2F506172656E7449442667743B266C743B2F726F772667743B266C743B436F6C756D6E557064617465642667743B4368617267654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B504B266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B427573696E657373547970654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765466F7254797065266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4368617267654465736372697074696F6E266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765416D6F756E74266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B437265617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4372656174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B557064617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B5570646174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B526F7756657273696F6E4E756D626572266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B476C6F62616C4964266C743B2F436F6C756D6E557064617465642667743B266C743B2F43757272536E617073686F742667743B266C743B2F456E746974792667743B266C743B2F526F6F742667743B2C20404E6F64654D697363436F6E74657874266C743B526F6F742667743B266C743B436F6C756D6E557064617465642667743B4368617267654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B504B266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B427573696E657373547970654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765466F7254797065266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4368617267654465736372697074696F6E266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765416D6F756E74266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B437265617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4372656174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B557064617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B5570646174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B526F7756657273696F6E4E756D626572266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B476C6F62616C4964266C743B2F436F6C756D6E557064617465642667743B266C743B2F526F6F742667743B3C2F436F6D6D616E643E3C53656E744461746554696D653E323031332D30362D30375431313A30313A34372E383633333032313C2F53656E744461746554696D653E3C2F5461736B3E
    SELECT @Message_BodyVBm1 'Message_BodyVBm1'

    DECLARE @Message_BodyVCm1 as varchar(MAX) = CAST(@Message_BodyVBm1 as varchar(MAX))
    SELECT @Message_BodyVCm1 'Message_BodyVCm1'

    DECLARE @Message_BodyVCm1XML as XML = CAST(@Message_BodyVCm1 as XML)
    SELECT @Message_BodyVCm1XML 'Message_BodyVCm1XML'

    DECLARE @Message_BodyVBm1XML as XML = CAST(@Message_BodyVBm1 as XML)
    select @Message_BodyVBm1XML 'Message_BodyVBm1XML'



    DECLARE @Message_BodyVBm2 as varbinary(MAX) = 0x3C5461736B3E3C5365727665724E616D653E4142435C464F4F3C2F5365727665724E616D653E3C44424E616D653E466F6F4261723C2F44424E616D653E3C436F6D6D616E643E7374557064617465466F6F42617220436F6E74657874536E617073686F743D20266C743B526F6F742667743B266C743B456E7469747920456E746974794E616D653D22436861726765222044617461436F6E746578744E616D653D22747255706461746543747822204170706C69636174696F6E496E7374616E63653D223261222046756C6C546578745365617263683D223022204375727254696D655374616D703D22323031332D30362D30372031313A30313A34372E38363333303231222052756C654E616D653D22436861726765734368616E676564222667743B266C743B43757272536E617073686F742667743B266C743B726F772667743B266C743B526F774E756D6265722667743B31266C743B2F526F774E756D6265722667743B266C743B427573696E6573735479706549642667743B323536266C743B2F427573696E6573735479706549642667743B266C743B436861726765416D6F756E742667743B3130302E30303030266C743B2F436861726765416D6F756E742667743B266C743B4368617267654465736372697074696F6E2667743B41747269627569E7E36F2064612074617861266C743B2F4368617267654465736372697074696F6E2667743B266C743B436861726765466F72547970652667743B30266C743B2F436861726765466F72547970652667743B266C743B43686172676549642667743B3537333837266C743B2F43686172676549642667743B266C743B49642667743B32303133303630372D303030352D31266C743B2F49642667743B266C743B504B2667743B313230333531266C743B2F504B2667743B266C743B4E6F646556616C75652667743B32303133303630372D303030352D31266C743B2F4E6F646556616C75652667743B266C743B506172656E7449442667743B32303133303630372D30303035266C743B2F506172656E7449442667743B266C743B2F726F772667743B266C743B436F6C756D6E557064617465642667743B4368617267654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B504B266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B427573696E657373547970654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765466F7254797065266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4368617267654465736372697074696F6E266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765416D6F756E74266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B437265617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4372656174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B557064617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B5570646174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B526F7756657273696F6E4E756D626572266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B476C6F62616C4964266C743B2F436F6C756D6E557064617465642667743B266C743B2F43757272536E617073686F742667743B266C743B2F456E746974792667743B266C743B2F526F6F742667743B2C20404E6F64654D697363436F6E74657874266C743B526F6F742667743B266C743B436F6C756D6E557064617465642667743B4368617267654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B504B266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B427573696E657373547970654964266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765466F7254797065266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4368617267654465736372697074696F6E266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B436861726765416D6F756E74266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B437265617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B4372656174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B557064617465644279266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B5570646174656444617465266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B526F7756657273696F6E4E756D626572266C743B2F436F6C756D6E557064617465642667743B266C743B436F6C756D6E557064617465642667743B476C6F62616C4964266C743B2F436F6C756D6E557064617465642667743B266C743B2F526F6F742667743B3C2F436F6D6D616E643E3C53656E744461746554696D653E323031332D30362D30375431313A30313A34372E383633333032313C2F53656E744461746554696D653E3C2F5461736B3E
    SELECT @Message_BodyVBm2 'Message_BodyVBm2'

    DECLARE @Message_BodyVCm2 as varchar(MAX) = CAST(@Message_BodyVBm2 as varchar(MAX))
    SELECT @Message_BodyVCm2 'Message_BodyVCm2'

    DECLARE @Message_BodyVCm2XML as XML = CAST(@Message_BodyVCm2 as XML)
    SELECT @Message_BodyVCm2XML 'Message_BodyVCm2XML'

    DECLARE @Message_BodyVBm2XML as XML = CAST(@Message_BodyVBm2 as XML)
    select @Message_BodyVBm2XML 'Message_BodyVBm2XML'

    --#####################################################

    Mittwoch, 12. Juni 2013 22:10
  • Ok. I think I know what's going on.

    The rules for character encoding of a varchar(max) and of XML are different.  varchar(max) uses a SQL Server encoding to map each byte to a character, and each of those bytes are converted to valid characters.  Subsequently when you cast the varchar(max) to XML all is well as you've already converted the bytes to characters acording to the rules of the encoding.

    However XML doesn't support the same encodings as SQL Server's varchar.  An XML parser will pick an encoding based on the first bytes and whether the document has an XML processing instruction.  The encoding the XML parser picked does not support the byte value that later appears in the string.  It doesn't know what character it is intended to be.

    The moral here is to cast the message_body back to whatever type it started as.  

    David 


    David http://blogs.msdn.com/b/dbrowne/


    Mittwoch, 12. Juni 2013 23:04