So you’re in BizTalk and you’ve created a schema based off a SQL Server stored procedure which includes a field of type ‘XML’. Everything works great, and then you hit the error:
The adapter failed to transmit message going to send port "Audit.Send" with URL "SQL://./xxxxxx/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80040e14" Description="XML parsing: line 1, character 1377, illegal qualified name character" ".
This error is thrown by SQL Server as it parses the string being passed into the SP for the XML field. Your SP looks something like
PROC [dbo].[prxxxx] @ID uniqueidentifier, @IncomingMessage xml = NULL INSERT Event(ID, IncomingMessage) VALUES (@ID, @IncomingMessage)
After much playing around with xml encoding (UTF-8 vs UTF-16 and ISO etc.) it still throws the same useless error (or the error ‘unable to switch the encoding’, but that’s caused by doing stupid things with XML encodings).
Looking at the outgoing message from the BizTalk pipeline, the character causing the issue was ‘_’, a perfectly valid XML character. However when looking at the SQL Server trace log, the innocuous underscore had become ‘+’ character….
Taking a close look at the XML we see:
... </a1:Exception> <a1:Exception_x002B_Xml xmlns:a1="http:// .... ...
This type of XML is created when serialising an object to a SOAP message, and as can be seen the ‘_’ characters is actually the start of a hex number for the character ‘+’ which magically gets decoded by SQL server before the SP. So the actual XML passed to the SP was:
... </a1:Exception> <a1:Exception+Xml xmlns:a1="http:// .... ...
So to fix this error we need to make sure any hex character references in our XML object are modified prior to being sent to SQL. In this case the XML document in SQL Server is for logging purposes only, so replacing all ‘_x0000_’ type values in the xml with ‘_x_0000_’ solves the issue perfectly.