SQL 2008 Querying Soap XML
- by Vince
I have been trying to process this SOAP XML return using SQL but all I get is NULL or nothing at all. I have tried different ways and pasted them all below.
Declare @xmlMsg xml;
Set @xmlMsg =
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<SendWarrantyEmailResponse xmlns="http://Web.Services.Warranty/">
<SendWarrantyEmailResult xmlns="http://Web.Services.SendWarrantyResult">
<WarrantyNumber>120405000000015</WarrantyNumber>
<Result>Cannot Send Email to anonymous account!</Result>
<HasError>true</HasError>
<MsgUtcTime>2012-06-07T01:11:36.8665126Z</MsgUtcTime>
</SendWarrantyEmailResult>
</SendWarrantyEmailResponse>
</soap:Body>
</soap:Envelope>';
declare @table table (data xml);
insert into @table values (@xmlMsg);
select data from @table;
WITH xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' as [soap], 'http://Web.Services.Warranty' as SendWarrantyEmailResponse, 'http://Web.Services.SendWarrantyResult' as SendWarrantyEmailResult)
SELECT Data.value('(/soap:Envelope[1]/soap:Body[1]/SendWarrantyEmailResponse[1]/SendWarrantyEmailResult[1]/WarrantyNumber[1])[1]','VARCHAR(500)') AS WarrantyNumber
FROM @Table ;
;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap],'http://Web.Services.Warranty' as SendWarrantyEmailResponse,'http://Web.Services.SendWarrantyResult' as SendWarrantyEmailResult)
--select @xmlMsg.value('(/soap:Envelope/soap:Body/SendWarrantyEmailResponse/SendWarrantyEmailResult/WarrantyNumber)[0]', 'nvarchar(max)')
--select T.N.value('.', 'nvarchar(max)') from @xmlMsg.nodes('/soap:Envelope/soap:Body/SendWarrantyEmailResponse/SendWarrantyEmailResult') as T(N)
select @xmlMsg.value('(/soap:Envelope/soap:Body/SendWarrantyEmailResponse/SendWarrantyEmailResult/HasError)[1]','bit') as test
;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap],'http://Web.Services.Warranty' as [SendWarrantyEmailResponse],'http://Web.Services.SendWarrantyResult' as [SendWarrantyEmailResult])
SELECT
SendWarrantyEmailResult.value('WarrantyNumber[1]','varchar(max)') AS WarrantyNumber,
SendWarrantyEmailResult.value('Result[1]','varchar(max)') AS Result,
SendWarrantyEmailResult.value('HasError[1]','bit') AS HasError,
SendWarrantyEmailResult.value('MsgUtcTime[1]','datetime') AS MsgUtcTime
FROM @xmlMsg.nodes('/soap:Envelope/soap:Body/SendWarrantyEmailResponse/SendWarrantyEmailResult') SendWarrantyEmailResults(SendWarrantyEmailResult)
