Error converting sp_OAMethod output to xml

Question

The following two SQL statements work fine most of the time. Lately I get an error in a specific case:

Msg 9420, Level 16, State 1, Procedure Alcom_Get_Customer_Details, Line 96 XML parsing: line 1, character 399, illegal xml character

The error generating statements are

Exec @ErrCode=sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

SELECT @XmlResponse as XmlResponse

select @XmlResponse = CAST(@ResponseText as xml)

When I examine the output of 'SELECT @XmlResponse as XmlResponse's statement it is only is only 361 characters. It looks like there are extraneous characters from 362 onwards.

How do I fix the error please?

Thank you.

Can you check content of @ResponseText? Does it actually contain illegal XML character? — har07
I checked the contents of @ResponseText. No, it does not contain any illegal characters. I opened the response as a binary file in VS editor. There is nothing past 362 column. — Piyush Varma
The @ResponseText is : <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="schemas.xmlsoap.org/soap/envelope" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema"> <soap:Body> <LookupCustomerIdResponse xmlns="alarm.com/WebServices"> <LookupCustomerIdResult>3797167</LookupCustomerIdResult> </LookupCustomerIdResponse> </soap:Body> </soap:Envelope> — Piyush Varma
After saving the output in a binary file and opening it in FireFox revealed illegal characters!.Yes @ResponseText contais an illegal character . How to keep only legal xml characters in T-Sql please? — Piyush Varma
SELECT ASCII('�'), ASCII('�') and the result is 63 for both those characters. I do see them correctly in SSMS editor. — Piyush Varma

Piyush Varma Piyush Varma · Accepted Answer · 2015-10-07T16:54:21

My colleague gave me a simple answer that allows only character codes 32 through 127. All other characters are removed:

CREATE FUNCTION dbo.RemoveNonASCII 
(
       @nstring nvarchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
       DECLARE @Result varchar(4000)
       SET @Result = ''

       DECLARE @nchar nvarchar(1)
       DECLARE @position int

       SET @position = 1
       WHILE @position <= LEN(@nstring)
       BEGIN
              SET @nchar = SUBSTRING(@nstring, @position, 1)

              IF UNICODE(@nchar) between 32 and 127
                     SET @Result = @Result + @nchar
              SET @position = @position + 1
       END

       RETURN @Result
END

Thank you to my colleague who found out UNICODE function.

Error converting sp_OAMethod output to xml

1 Answers