(SQL Server) Demonstrate the XML "I" Property

Demonstrates the XML "I" property. The properties I, J, and K can be used in paths to access the I'th, J'th, or K'th child in a path. Three indexing properties are provided to allow for triple-nested loops using this feature.

Note: This example requires Chilkat v9.5.0.64 or later.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example uses the XML document at https://www.chilkatsoft.com/exampleData/xero_accounts.xml
    -- The data is a download of the Xero accounts for the sandbox company test data (it's not real data).

    -- We'll use Chilkat HTTP to download the XML.
    -- This example assumes the Chilkat HTTP API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Download the XML from https://www.chilkatsoft.com/exampleData/xero_accounts.xml
    -- into sbXml
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://www.chilkatsoft.com/exampleData/xero_accounts.xml'
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @url, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @bAutoTrim int
    SELECT @bAutoTrim = 0
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadSb', @success OUT, @sbXml, @bAutoTrim

    -- How many accounts exist?
    DECLARE @numAccounts int
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @numAccounts OUT, 'Accounts'

    PRINT 'numAccounts = ' + @numAccounts

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numAccounts
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i

        EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Accounts|Account[i]|AccountID'
        PRINT 'AccountID: ' + @sTmp0

        EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Accounts|Account[i]|Name'
        PRINT 'Name: ' + @sTmp0

        EXEC sp_OAMethod @xml, 'GetChildIntValue', @iTmp0 OUT, 'Accounts|Account[i]|Code'
        PRINT 'Code: ' + @iTmp0

        EXEC sp_OAMethod @xml, 'GetChildBoolValue', @iTmp0 OUT, 'Accounts|Account[i]|EnablePaymentsToAccount'
        PRINT 'EnablePaymentsToAccount: ' + @iTmp0

        PRINT '----'
        SELECT @i = @i + 1
      END

    -- The output looks like this:

    -- 	numAccounts = 69
    -- 	AccountID: ceef66a5-a545-413b-9312-78a53caadbc4
    -- 	Name: Checking Account
    -- 	Code: 90
    -- 	EnablePaymentsToAccount: False
    -- 	----
    -- 	AccountID: 3d09fd49-434d-4c18-a57b-831663ab70d2
    -- 	Name: Savings Account
    -- 	Code: 91
    -- 	EnablePaymentsToAccount: False
    -- 	----
    -- 	AccountID: 5f5e1b00-5331-4ee5-bc84-39dbd9a27db3
    -- 	Name: Accounts Receivable
    -- 	Code: 120
    -- 	EnablePaymentsToAccount: False
    -- 	----
    -- 	AccountID: b0a23f8d-1b6d-4209-96f9-8046f794e1f4
    -- 	Name: Prepayments
    -- 	Code: 130
    -- 	EnablePaymentsToAccount: False
    -- 	----
    -- 	...

    -- The xero_accounts.xml file contains data that looks like this:

    -- 	<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    -- 	  <Id>409d062b-d3c2-4062-99a6-31b7c1c14662</Id>
    -- 	  <Status>OK</Status>
    -- 	  <ProviderName>ChilkatPrivate</ProviderName>
    -- 	  <DateTimeUTC>2016-11-01T22:30:13.3606258Z</DateTimeUTC>
    -- 	  <Accounts>
    -- 	    <Account>
    -- 	      <AccountID>ceef66a5-a545-413b-9312-78a53caadbc4</AccountID>
    -- 	      <Code>090</Code>
    -- 	      <Name>Checking Account</Name>
    -- 	      <Status>ACTIVE</Status>
    -- 	      <Type>BANK</Type>
    -- 	      <TaxType>NONE</TaxType>
    -- 	      <Class>ASSET</Class>
    -- 	      <EnablePaymentsToAccount>false</EnablePaymentsToAccount>
    -- 	      <ShowInExpenseClaims>false</ShowInExpenseClaims>
    -- 	      <BankAccountNumber>132435465</BankAccountNumber>
    -- 	      <BankAccountType>BANK</BankAccountType>
    -- 	      <CurrencyCode>USD</CurrencyCode>
    -- 	      <ReportingCode>ASS</ReportingCode>
    -- 	      <ReportingCodeName>Assets</ReportingCodeName>
    -- 	      <HasAttachments>false</HasAttachments>
    -- 	      <UpdatedDateUTC>2016-10-15T22:22:44.53</UpdatedDateUTC>
    -- 	    </Account>
    -- 	    <Account>
    -- 	      <AccountID>3d09fd49-434d-4c18-a57b-831663ab70d2</AccountID>
    -- 	      <Code>091</Code>
    -- 	      <Name>Savings Account</Name>
    -- 	      <Status>ACTIVE</Status>
    -- 	      <Type>BANK</Type>
    -- 	      <TaxType>NONE</TaxType>
    -- 	      <Class>ASSET</Class>
    -- 	      <EnablePaymentsToAccount>false</EnablePaymentsToAccount>
    -- 	      <ShowInExpenseClaims>false</ShowInExpenseClaims>
    -- 	      <BankAccountNumber>987654321</BankAccountNumber>
    -- 	      <BankAccountType>BANK</BankAccountType>
    -- 	      <CurrencyCode>USD</CurrencyCode>
    -- 	      <ReportingCode>ASS</ReportingCode>
    -- 	      <HasAttachments>false</HasAttachments>
    -- 	      <UpdatedDateUTC>2016-10-15T22:22:44.53</UpdatedDateUTC>
    -- 	    </Account>
    -- 	    ...
    -- 

    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @xml


END
GO