(SQL Server) Demonstrate the XML I, J, and K Properties

Demonstrates the XML I, J, and K properties. These properties 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)
    -- Note: This example requires Chilkat v9.5.0.64 or later.

    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

    EXEC sp_OASetProperty @xml, 'Tag', 'tripleNested'

    DECLARE @i int
    SELECT @i = 0
    DECLARE @j int
    SELECT @j = 0
    DECLARE @k int
    SELECT @k = 0

    DECLARE @sbValue int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbValue OUT

    DECLARE @bAutoCreate int
    SELECT @bAutoCreate = 1

    WHILE @i < 3
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        SELECT @j = 0
        WHILE @j < 4
          BEGIN
            EXEC sp_OASetProperty @xml, 'J', @j
            SELECT @k = 0
            WHILE @k < 2
              BEGIN
                EXEC sp_OASetProperty @xml, 'K', @k

                DECLARE @n int
                SELECT @n = @i * 100 + @j * 10 + @k
                EXEC sp_OAMethod @sbValue, 'Clear', NULL
                DECLARE @success int
                EXEC sp_OAMethod @sbValue, 'AppendInt', @success OUT, @n

                EXEC sp_OAMethod @sbValue, 'GetAsString', @sTmp0 OUT
                EXEC sp_OAMethod @xml, 'UpdateAt', @success OUT, 'a[i]|b[j]|c[k]', @bAutoCreate, @sTmp0
                SELECT @k = @k + 1
              END
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- This is the XML
    -- See below for code showing how to parse it..

    -- 	<?xml version="1.0" encoding="utf-8" ?>
    -- 	<tripleNested>
    -- 	    <a>
    -- 	        <b>
    -- 	            <c>0</c>
    -- 	            <c>1</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>10</c>
    -- 	            <c>11</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>20</c>
    -- 	            <c>21</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>30</c>
    -- 	            <c>31</c>
    -- 	        </b>
    -- 	    </a>
    -- 	    <a>
    -- 	        <b>
    -- 	            <c>100</c>
    -- 	            <c>101</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>110</c>
    -- 	            <c>111</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>120</c>
    -- 	            <c>121</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>130</c>
    -- 	            <c>131</c>
    -- 	        </b>
    -- 	    </a>
    -- 	    <a>
    -- 	        <b>
    -- 	            <c>200</c>
    -- 	            <c>201</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>210</c>
    -- 	            <c>211</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>220</c>
    -- 	            <c>221</c>
    -- 	        </b>
    -- 	        <b>
    -- 	            <c>230</c>
    -- 	            <c>231</c>
    -- 	        </b>
    -- 	    </a>
    -- 	</tripleNested>
    -- 

    -- Parse the XML, without needing to know the number of children.
    -- That can be discovered with the NumChildrenAt method.

    SELECT @i = 0
    DECLARE @aCount int
    EXEC sp_OAGetProperty @xml, 'NumChildren', @aCount OUT
    WHILE @i < @aCount
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        SELECT @j = 0
        DECLARE @bCount int
        EXEC sp_OAMethod @xml, 'NumChildrenAt', @bCount OUT, 'a[i]'
        WHILE @j < @bCount
          BEGIN
            EXEC sp_OASetProperty @xml, 'J', @j
            SELECT @k = 0
            DECLARE @cCount int
            EXEC sp_OAMethod @xml, 'NumChildrenAt', @cCount OUT, 'a[i]|b[j]'
            WHILE @k < @cCount
              BEGIN
                EXEC sp_OASetProperty @xml, 'K', @k



                EXEC sp_OAMethod @xml, 'GetChildIntValue', @iTmp0 OUT, 'a[i]|b[j]|c[k]'
                PRINT @i + ', ' + @j + ', ' + @k + ': ' + @iTmp0
                SELECT @k = @k + 1
              END
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    -- Output:

    -- 	0, 0, 0: 0
    -- 	0, 0, 1: 1
    -- 	0, 1, 0: 10
    -- 	0, 1, 1: 11
    -- 	0, 2, 0: 20
    -- 	0, 2, 1: 21
    -- 	0, 3, 0: 30
    -- 	0, 3, 1: 31
    -- 	1, 0, 0: 100
    -- 	1, 0, 1: 101
    -- 	1, 1, 0: 110
    -- 	1, 1, 1: 111
    -- 	1, 2, 0: 120
    -- 	1, 2, 1: 121
    -- 	1, 3, 0: 130
    -- 	1, 3, 1: 131
    -- 	2, 0, 0: 200
    -- 	2, 0, 1: 201
    -- 	2, 1, 0: 210
    -- 	2, 1, 1: 211
    -- 	2, 2, 0: 220
    -- 	2, 2, 1: 221
    -- 	2, 3, 0: 230
    -- 	2, 3, 1: 231
    -- 

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @sbValue


END
GO