(SQL Server) Access an Inner Node of an XML Document

Demonstrates how to navigate to a particular inner node of an XML document.

The XML sample used in this example is also available here: Sample SOAP XML Document

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- This example navigates to the "qualificationData" node of the XML document shown below.

    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

    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/xml/soapDoc.xml'

    DECLARE @qualData int
    EXEC sp_OAMethod @xml, 'FindChild', @qualData OUT, 'soap:Body|v1:createEvents|events|qualificationData'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

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

    -- Iterate over the "instructions" child nodes and show the value of each.
    DECLARE @n int
    EXEC sp_OAMethod @qualData, 'NumChildrenHavingTag', @n OUT, 'instructions'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @strValue nvarchar(4000)

    WHILE @i < @n
      BEGIN
        EXEC sp_OASetProperty @qualData, 'I', @i
        EXEC sp_OAMethod @qualData, 'GetChildContent', @strValue OUT, 'instructions[i]|value'
        EXEC sp_OAGetProperty @qualData, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN
            -- A node at the tag path existed (i.e. the "value" node existed)


            PRINT @i + ': ' + @strValue
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @qualData

    -- <?xml version="1.0"?>
    -- <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:v1="http://www.mywebsite.com/v6/MyEvent">
    --     <soap:Header>
    --         <ROClientIDHeader soap:mustUnderstand="0" xmlns="http://tempuri.org/">
    --             <ID>{69CE6D9E-B75E-422F-A79C-D4918192EAD9}</ID>
    --         </ROClientIDHeader>
    --     </soap:Header>
    --     <soap:Body>
    --         <v1:createEvents xmlns:v1="http://www.mywebsite.com/v6/MyEvent">
    --             <events>
    --                 <coreData>
    --                     <anomaly>false</anomaly>
    --                     <contacts>
    --                         <coordinates>Mobile</coordinates>
    --                         <flags>0</flags>
    --                         <label>0606060606</label>
    --                         <type>MOBILE</type>
    --                     </contacts>
    --                     <contacts>
    --                         <coordinates>Telephone</coordinates>
    --                         <flags>0</flags>
    --                         <label>040400404</label>
    --                         <type>PHONE</type>
    --                     </contacts>
    --                     <contacts>
    --                         <coordinates>Fax</coordinates>
    --                         <flags>0</flags>
    --                         <label>0505050505</label>
    --                         <type>FAX</type>
    --                     </contacts>
    --                     <contacts>
    --                         <coordinates>Email</coordinates>
    --                         <flags>0</flags>
    --                         <label>test@test.com</label>
    --                         <type>EMAIL</type>
    --                     </contacts>
    --                     <creationDate>2017-03-21T09:43:04.043</creationDate>
    --                     <description>Buble Tea Bar</description>
    --                     <earliestDate>2017-03-25T00:00:00</earliestDate>
    --                     <expirationDate>2017-04-03T00:00:00</expirationDate>
    --                     <orderingCustomer>ACME INTERNATIONAL</orderingCustomer>
    --                     <priority>0</priority>
    --                     <referentialData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:externalReferentialData">
    --                         <customerName>ACME INTERNATIONAL</customerName>
    --                         <equipmentName />
    --                         <location>
    --                             <address>adresseSite</address>
    --                             <city>VilleSite</city>
    --                             <contact>Sarah Croche</contact>
    --                             <description>DescriptionSite</description>
    --                             <name>Male �  bar</name>
    --                             <zipCode>69007</zipCode>
    --                         </location>
    --                     </referentialData>
    --                 </coreData>
    --                 <id>INTERV00102</id>
    --                 <message />
    --                 <qualificationData>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>description</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>Intervention �  realiser</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>customerName</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>ACME INTERNATIONAL</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>orderingCustomer</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>ACME INTERNATIONAL</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Description</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>Buble Tea Bar</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Name</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>Male �  bar</value>
    --                         <valueCode />
    --                    </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>earliestDate</id>
    --                         <fillingDate>2017-03-20T00:00:00</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>expirationDate</id>
    --                         <fillingDate>2017-03-24T00:00:00</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Acompte</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>200</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Affaire</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>AFF56</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>DelaiStandard</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>off</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>address</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                        <value>adresseSite</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                        <id>city</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>VilleSite</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>zipCode</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>69007</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>description</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>DescriptionSite</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>ID</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>ENT</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>creationDate</id>
    --                         <fillingDate>2017-03-21T09:43:04.043</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>MOBILE</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>0606060606</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>PHONE</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>040400404</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>FAX</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>0505050505</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>EMAIL</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>test@test.com</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>contact</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>Sarah Croche</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>DatePrevPose</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>SoldeAPayer</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Id</id>
    --                         <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                         <value>tech1</value>
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>appointmentDate</id>
    --                         <fillingDate>2017-03-22T10:00:00.607</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>schedulingDate</id>
    --                         <fillingDate>2017-03-22T11:00:00.607</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>schedulingEndDate</id>
    --                         <fillingDate>2017-03-22T12:00:00.607</fillingDate>
    --                         <value />
    --                         <valueCode />
    --                     </instructions>
    --                     <instructions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:reportField">
    --                         <id>Tableau2</id>
    --                         <rows>
    --                             <id>Ligne 1</id>
    --                             <cells>
    --                                 <id>objet</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>Fenêtre</value>
    --                                 <valueCode />
    --                             </cells>
    --                             <cells>
    --                                 <id>largeur</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>100</value>
    --                                 <valueCode />
    --                             </cells>
    --                             <cells>
    --                                 <id>hauteur</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>50</value>
    --                                 <valueCode />
    --                             </cells>
    --                         </rows>
    --                         <rows>
    --                             <id>Ligne 2</id>
    --                             <cells>
    --                                 <id>objet</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>Porte</value>
    --                                 <valueCode />
    --                             </cells>
    --                             <cells>
    --                                 <id>largeur</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>220</value>
    --                                 <valueCode />
    --                             </cells>
    --                             <cells>
    --                                 <id>hauteur</id>
    --                                 <fillingDate>1899-12-30T00:00:00</fillingDate>
    --                                 <value>100</value>
    --                                 <valueCode />
    --                             </cells>
    --                         </rows>
    --                     </instructions>
    --                     <type>
    --                         <duration>0</duration>
    --                         <id>ENT</id>
    --                     </type>
    --                </qualificationData>
    --                 <remoteStatus />
    --                 <schedulingData>
    --                     <agentId xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="v1:externalEntityId">
    --                         <id>tech1</id>
    --                     </agentId>
    --                     <appointmentDate>2017-03-28T10:00:00.607</appointmentDate>
    --                     <schedulingDate>2017-03-28T12:00:00.607</schedulingDate>
    --                     <schedulingEndDate>2017-03-28T14:00:00.607</schedulingEndDate>
    --                     <useSchedulingHour>true</useSchedulingHour>
    --                 </schedulingData>
    --                 <status>NEW</status>
    --             </events>
    --         </v1:createEvents>
    --     </soap:Body>
    -- </soap:Envelope>

    EXEC @hr = sp_OADestroy @xml


END
GO