Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to determine LookupCustomerIdResult value from following XML SOAP 1.1 response:
HTML
Declare @ResponseText as Varchar(8000);
SET @ResponseText = '
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <LookupCustomerIdResponse xmlns="http://www.alarm.com/WebServices">
      <LookupCustomerIdResult>54949945</LookupCustomerIdResult>
    </LookupCustomerIdResponse>
  </soap:Body>
</soap:Envelope>'


I tried following but I get NULL result:

Declare @XmlResponse as xml;
select @XmlResponse = CAST(@ResponseText as xml)

DECLARE @customerID VARCHAR(500);

SELECT @customerID = @XmlResponse.value('(/LookupCustomerIdResponse/LookupCustomerIdResult)[1]', 'varchar(550)')

SELECT @customerID

Thank you.
Posted
Comments
RedDk 5-Aug-15 19:41pm    
Here's SOAP 1.1 info:
http://www.w3.org/TR/2000/NOTE-SOAP-20000508/

And just off hand, after running a VS2010 help search on "SOAP 1.1 AND SQL" and getting only 28 returns of mainly nuance @ "XMLA", I'd say you're going to hit a wall of parsing difficulties in XML due to "prefix not supported". Which means an intermediate replacement of un-parsable characters is going to have to be wrapped up by you in the TSQL somewhere.

Incidently, your SELECT statements should be SET statements ... etc. Sort of ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900