SQL Server

Find XML Element where Attribute = Value

See more XML Examples

Finds an XML element at a particular location where an attribute has a specified value. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- We have the following XML and wish to find the value of the time attribute where msg = "alert-from".

    -- <cdr id="4e5d3e7f41bf6201ad81000c29703270" e164="6207">
    --     <user>
    --         <grp name="wfln" mode="active"/>
    --     </user>
    --     <event msg="setup-to" time="287069" e164="5034"/>
    --     <event msg="alert-from" time="287069" e164="5034"/>
    --     <event msg="rel-to" time="287079" e164="5034"/>
    -- </cdr>

    -- First, build the above XML:

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'cdr'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'id', '4e5d3e7f41bf6201ad81000c29703270'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'e164', '6207'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'user|grp', 1, 'name', 'wfln'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'user|grp', 1, 'mode', 'active'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event', 1, 'msg', 'setup-to'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event', 1, 'time', '287069'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event', 1, 'e164', '5034'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[1]', 1, 'msg', 'alert-from'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[1]', 1, 'time', '287069'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[1]', 1, 'e164', '5034'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[2]', 1, 'msg', 'rel-to'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[2]', 1, 'time', '287079'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'event[2]', 1, 'e164', '5034'

    -- Show that we have the above XML
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Find the element where msg = "alert-from"
    -- This updates the xml object's reference to the found element (if successful).
    DECLARE @notUsed nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @notUsed OUT, '/A/event,msg,alert-from|$'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Not found.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Get the value of the "time" attribute.

    EXEC sp_OAMethod @xml, 'GetAttrValue', @sTmp0 OUT, 'time'
    PRINT 'time = ' + @sTmp0

    -- Restore the xml object's internal reference to the root of the XML document
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    EXEC @hr = sp_OADestroy @xml


END
GO