Post reply

Valid XML causing Service Broker queue to Error

  • March 28, 2012 at 3:39 am

    #266367

    Hi - I have a problem that I hope somebody can throw some light on. We use SB a lot to pass data around our system. All message types are specified as Well Formed XML.

    We generate an XML message using SELECT ... FOR XML in T-SQL, but if the resulting XML contains a "£" sign (GBP currency symbol) then transmission of the XML causes a validation error at the far end, reporting "illegal character".

    I can't understand how XML generated at one end of a conversation can not be valid by the time it reaches the far end.

    Here's an example of a simple SELECT that will create the problem (this is very similar to the sort of thing we do, but usually with table-derived values for some of the attributes - it makes no difference to the outcome):

    DECLARE @xml VARCHAR(max)

    SET @xml = (

    SELECT 'Type' = 'InboundSMSPosted', 'Recipient' = '+4477blahblah', 'MessageBody' = 'Buy one of these for only £9.99 from your nearest ServiceBroker store'

    FOR XML RAW('SMSEvent')

    )

    SELECT CAST(@xml as XML)

    This produces XML, as you would expect:

    <SMSEvent Type="InboundSMSPosted" Recipient="+4477blahblah", MessageBody="Buy one of these for only £9.99 from your nearest ServiceBroker store" />

    We then send it to the far end, where the error is triggered. If we send the same message without the pound sign, it is perfectly happy. We've found a few other characters that cause the same problem - strange quotation marks and an elongated dash among them. Those we are happy to detect and replace by 'proper' equivalents, but the pound symbol is pretty fundamental.

    I'd like to get to the bottom of this - if anyone can help I'd be very grateful. We are using SQL Server 2008 Workgroup Ed. on Server 2008 R2.

    Regards, Bob.

  • March 28, 2012 at 5:40 am

    #1465592

    that is not a valid xml/html entity character;

    it has to be escaped as [amp]pound; or & pound;(no space between...hard to describe html on the forum)

    http://www.w3schools.com/html/html_entities.asp

    the full list of entities to be escaped:

    http://www.w3schools.com/tags/ref_entities.asp

    I believe you want to use the .HTMLEncode function in .NET on your data prior to turning it into xml.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 28, 2012 at 5:46 am

    #1465596

    Thanks, Lowell. That sort of makes sense, but why, then, does not the SELECT ... FOR XML do that escaping in the first place? If that isn't guaranteed to be 'xml-safe' then surely T-SQL XML handling is seriously flawed?

    Bob

  • March 28, 2012 at 5:59 am

    #1465603

    Bob Cullen-434885 (3/28/2012)

    Thanks, Lowell. That sort of makes sense, but why, then, does not the SELECT ... FOR XML do that escaping in the first place? If that isn't guaranteed to be 'xml-safe' then surely T-SQL XML handling is seriously flawed?

    Bob

    wow, yes i would expect FOR XML to escape everything for you;

    i'm going to test that now, I've got to see if this is an issue...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply