Insert Special Characters from XML into SQL

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Insert Special Characters from XML into SQL

Viewing 1 reply thread
  • Author
    Posts
    • March 2, 2006 at 4:52 PM #14399
      community-content

        Hello Everyone.

        I have a Web Service that was created using the BizTalk Web Services Publishing Wizard, which accepts a XML Schema. After the Web Service accepts the schema, it goes through some processing and then is sent to a SQL Stored Procedure for inserting into the database.

        The problem that i have is when special characters exist in one of the attributes in the schema, and is attempt to be inserted into SQL an error occurs.

        ie: FirstName = \”Steve%u00e7%u00e3o’s\”

        ERROR: The adapter \”SQL\” raised an error message. Details \”HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The statement has been terminated.\”
        HRESULT=\”0x80040e14\” Description=\”Could not find prepared statement with handle 0.\”
        HRESULT=\”0x80040e14\” Description=\”The error description is ‘An invalid character was found in text content.’.\”
        HRESULT=\”0x80040e14\” Description=\”The XML parse error 0xc00ce508 occurred on line number 2, near the XML text \”<PurchaseOrder xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\” xmlns:xsd=\”http://www.w3.org/2001/XMLSchema\” OrderId=\”3111c04c-f6c5-4bb1-bdc7-c60efd5c7fd2\” StatusCode=\”1\” FirstName=\”Steve\”.\”
        \”.

        My Sproc accepts a TEXT Parameter and does Inserts using FROM OPENXML…

        So, I guess my question would be how do I handle Special Characters being inserted into SQL?

        Thanks Kindly,
        Steven Campbell

      • March 2, 2006 at 8:28 PM #14400
        community-content

          Hey greg.forsythe,

          Thanks for your response.
          I changed the sproc parameter from Text to nText and it all seems to work good.

          Have a good day.
          — Steven

          • March 2, 2006 at 6:38 PM #14401
            community-content

              Xml is very specific about character encoding.
              Unless specified otherwise the default encoding is UTF-8.
              <?xml version=\”1.0\” encoding=\”UTF-8\”?>

              You can set the encoding to ISO8259-1 (US-ASCII) on the incoming message
              <?xml version=\”1.0\” encoding=\”ISO-8259-1\”?>

              or possibly use UTF-16 – although this will require changing the data, also using ntext rather than text in SQL

        • Author
          Posts
        Viewing 1 reply thread
        • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.