|
|
(SQL Server) Parse SOAP XML Response
Code to parse a SOAP XML response.
This is the SOAP response parsed by the code below:
34.5
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
DECLARE @bFound int
DECLARE @xml int
-- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Load the XML object with the SOAP response.
-- The XML may be loaded from a file by calling
-- LoadXmlFile, or directly from a string by calling
-- LoadXml.
EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'soapResponse.xml'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
RETURN
END
-- The quickest way to get a piece of data is w/ the
-- ChilkatPath method.
-- To get the content of the m:Price node:
DECLARE @strPrice nvarchar(4000)
EXEC sp_OAMethod @xml, 'ChilkatPath', @strPrice OUT, 'soap:Body|m:GetStockPriceResponse|m:Price|*'
PRINT 'Price = ' + @strPrice
-- ------------------------------------
-- Another way of doing it:
-- Another way to get it is to navigate to the node.
-- Navigate to soap:Body
EXEC sp_OAMethod @xml, 'FirstChild2', @success OUT
-- Navigate to m:GetStockPriceResponse
-- You may call FirstChild2, or you may call GetChildWithTag2
-- to get the Nth direct child having a given tag.
-- In this case, get the 1st direct child having the tag
-- "m:GetStockPriceResponse"
-- (Indexing begins at 0, so the 1st occurrence is at index 0.)
EXEC sp_OAMethod @xml, 'GetNthChildWithTag2', @bFound OUT, 'm:GetStockPriceResponse', 0
IF @bFound <> 1
BEGIN
PRINT 'Did not find m:GetStockPriceResponse node.'
EXEC @hr = sp_OADestroy @xml
RETURN
END
-- One more to go..
EXEC sp_OAMethod @xml, 'GetNthChildWithTag2', @bFound OUT, 'm:Price', 0
IF @bFound <> 1
BEGIN
PRINT 'Did not find m:Price node.'
EXEC @hr = sp_OADestroy @xml
RETURN
END
-- The content of the "m:Price" node is "34.5"
EXEC sp_OAGetProperty @xml, 'Content', @strPrice OUT
PRINT 'Price = ' + @strPrice
-- Restore the xml object's internal reference to the root
-- of the XML document:
EXEC sp_OAMethod @xml, 'GetRoot2', NULL
-- ------------------------------------
-- Another way of doing it:
-- If you know that the node w/ the content you want to
-- extract is uniquely tagged (i.e. no other node has the same
-- tag), then search for the node having the tag:
EXEC sp_OAMethod @xml, 'SearchForTag2', @bFound OUT, @xml, 'm:Price'
IF @bFound <> 1
BEGIN
-- Not found...
EXEC @hr = sp_OADestroy @xml
RETURN
END
-- The content of the "m:Price" node is "34.5"
EXEC sp_OAGetProperty @xml, 'Content', @strPrice OUT
PRINT 'Price = ' + @strPrice
-- Restore the xml object's internal reference to the root
-- of the XML document:
EXEC sp_OAMethod @xml, 'GetRoot2', NULL
-- ------------------------------------
-- Another way of doing it:
-- Again, assuming there is only a single node with the
-- tag to be fetched:
EXEC sp_OAMethod @xml, 'AccumulateTagContent', @strPrice OUT, 'm:Price', ''
PRINT 'Price = ' + @strPrice
-- There are many other possible ways of doing the same thing..
EXEC @hr = sp_OADestroy @xml
END
GO
|
