SQL Server
SQL Server
Access an Inner Node of an XML Document
See more XML Examples
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 SQL Server Downloads
-- 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
DECLARE @success int
SELECT @success = 0
-- This example navigates to the "qualificationData" node of the XML document shown below.
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_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