Loop Thru XML Tags with Cursor and Insert Parent/Child data into a table.

Question

Post reply

Loop Thru XML Tags with Cursor and Insert Parent/Child data into a table.

dave5of5

SSC Journeyman

Points: 81
More actions
October 22, 2018 at 9:15 am

#409075

So, here's the XML.. I am relying on order of presence in file.
<?xml version="1.0"?>
<ROOT>
    <Policy>
        <PolicyNumber>HO00000001</PolicyNumber>
        <TransactionType>NB</TransactionType>
        <AccountingDate>082018</AccountingDate>
        <PolicyEffectiveDate>08302018</PolicyEffectiveDate>
        <PolicyExpirationDate>08302019</PolicyExpirationDate>
        <ChangeDate>08302018</ChangeDate>
        <Coverages>
            <Coverage>
                <CoverageCode>PL</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>110000</Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium>11.00</Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>MEDPM</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>1000</Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium></Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>PI</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1></Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium>11.00</Premium>
            </Coverage>
        </Coverages>
    </Policy>
    <Policy>
        <PolicyNumber>HO00000001</PolicyNumber>
        <TransactionType>AM</TransactionType>
        <AccountingDate>093018</AccountingDate>
        <PolicyEffectiveDate>08302018</PolicyEffectiveDate>
        <PolicyExpirationDate>08302019</PolicyExpirationDate>
        <ChangeDate>09302018</ChangeDate>
        <Coverages>
            <Coverage>
                <CoverageCode>PL</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>110000</Limit1>
                <Limit2>120000</Limit2>
                <Limit3>130000</Limit3>
                <Premium>31.00</Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>MEDPM</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>1000</Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium></Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>PI</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1></Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium>11.00</Premium>
            </Coverage>
        </Coverages>
    </Policy>
    <Policy>
        <PolicyNumber>HO00000001</PolicyNumber>
        <TransactionType>AM</TransactionType>
        <AccountingDate>093018</AccountingDate>
        <PolicyEffectiveDate>08302018</PolicyEffectiveDate>
        <PolicyExpirationDate>08302019</PolicyExpirationDate>
        <ChangeDate>09302018</ChangeDate>
        <Coverages>
            <Coverage>
                <CoverageCode>PL</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>110001</Limit1>
                <Limit2>120002</Limit2>
                <Limit3>130003</Limit3>
                <Premium>34.00</Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>MEDPM</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>1000</Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium></Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>PI</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1></Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium>11.00</Premium>
            </Coverage>
        </Coverages>
    </Policy>
    <Policy>
        <PolicyNumber>HO00000001</PolicyNumber>
        <TransactionType>RN</TransactionType>
        <AccountingDate>082019</AccountingDate>
        <PolicyEffectiveDate>08302019</PolicyEffectiveDate>
        <PolicyExpirationDate>08302020</PolicyExpirationDate>
        <ChangeDate>08302018</ChangeDate>
        <Coverages>
            <Coverage>
                <CoverageCode>PL</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>110000</Limit1>
                <Limit2>120000</Limit2>
                <Limit3>130000</Limit3>
                <Premium>31.00</Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>MEDPM</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1>1000</Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium></Premium>
            </Coverage>
            <Coverage>
                <CoverageCode>PI</CoverageCode>
                <Exposure></Exposure>
                <DeductibleType></DeductibleType>
                <DeductibleAmount></DeductibleAmount>
                <Limit1></Limit1>
                <Limit2></Limit2>
                <Limit3></Limit3>
                <Premium>11.00</Premium>
            </Coverage>
        </Coverages>
    </Policy>
</ROOT>
<Policy> is the parent and <Coverages> are the children.
I'm trying to cursor thru the above XML, processing one <Policy>.</Policy> at a time and insert that data into a table. As you can see, there can be multiple <Policy> nodes with the same data (PolicyNumber, TransactionType, AccountingDate, PolicyEffectiveDate, PolicyExpirationDate, ChangeDate).
What I'm running into is that when I try to process the <Coverages>...</Coverages> for the specific <Policy>.</Policy> that I'm on I end up getting all of the <Coverages>...<Coverages> in the XML.
Any idea on how I can get only those <Coverages>...</Coverages> for the <Policy>.</Policy> that I'm working with?
Here's my code:
USE CompanyImport
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK '\\ASI3\ASI\ASI Projects\Stat Reporting\Documentation\Table Definitions\Stat Reporting Input Layout-TestData_MultipleTrans_OnePolicy.xml', SINGLE_BLOB) AS x;
DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML WHERE ID = '1' -- The row to process
-- IDENTITY IDs
DECLARE @PolicyID BIGINT
DECLARE @ProductID BIGINT
--*** BEGIN POLICY PROCESSING ***
-- Policy Columns
DECLARE @CompanyID SMALLINT
DECLARE @PolicyNumber VARCHAR(50)
DECLARE @TransactionType VARCHAR(50)
DECLARE @EffectiveDate VARCHAR(50)
DECLARE @ExpirationDate VARCHAR(50)
DECLARE @ChangeDate VARCHAR(50)
DECLARE @AccountingDate VARCHAR(MAX)
DECLARE @AccountingYear VARCHAR(50)
DECLARE @AccountingMonth VARCHAR(50)
-- Default Company to 1
SET @CompanyID = 1
-- Loop thru XML and process each Policy
DECLARE POLICY_CURSOR CURSOR LOCAL FAST_FORWARD FOR
    SELECT s.c.value('(PolicyNumber/text())[1]', 'nvarchar(max)') as PolicyNumber,
         s.c.value('(TransactionType/text())[1]', 'nvarchar(max)') as TransactionType,
         s.c.value('(PolicyEffectiveDate/text())[1]', 'nvarchar(max)') as EffectiveDate,
         s.c.value('(PolicyExpirationDate/text())[1]', 'nvarchar(max)') as ExpirationDate,
         s.c.value('(ChangeDate/text())[1]', 'nvarchar(max)') as ChangeDate,
         s.c.value('(AccountingDate/text())[1]', 'nvarchar(max)') as AccountingDate
    FROM @XML.nodes('ROOT') AS m(c)
    OUTER APPLY m.c.nodes('Policy') as s(c)
OPEN POLICY_CURSOR
FETCH NEXT FROM POLICY_CURSOR INTO @PolicyNumber, @TransactionType, @EffectiveDate, @ExpirationDate, @ChangeDate, @AccountingDate
WHILE (@@FETCH_STATUS = 0) BEGIN
    BEGIN TRY
        SET @POLICYCOUNT = @POLICYCOUNT + 1
        ---- Insert Policy Record
        SET @AccountingYear = SUBSTRING(@AccountingDate,3,2)
        SET @AccountingMonth = SUBSTRING(@AccountingDate,1,2)
        PRINT 'Policy = ' + CONVERT (VARCHAR, @PolicyNumber)
        PRINT 'Trans = ' + CONVERT (VARCHAR, @TransactionType)
        PRINT 'Eff = ' + CONVERT (VARCHAR, @EffectiveDate)
        PRINT 'Exp = ' + CONVERT (VARCHAR, @ExpirationDate)
        PRINT 'Chg = ' + CONVERT (VARCHAR, @ChangeDate)
        PRINT 'AcctYr = ' + CONVERT (VARCHAR, @AccountingYear)
        PRINT 'AcctMo = ' + CONVERT (VARCHAR, @AccountingMonth)
        PRINT '**************************************************'
        --*** BEGIN POLICY COVERAGE PROCESSING ***
        -- Coverage Columns
        DECLARE @CoverageCode VARCHAR(50)
        DECLARE @Limit1 VARCHAR(50)
        DECLARE @Limit2 VARCHAR(50)
        DECLARE @Limit3 VARCHAR(50)
        DECLARE @Exposure VARCHAR(50)
        DECLARE @Premium VARCHAR(50)
        DECLARE @ClassCode VARCHAR(50)
        DECLARE @DeductibleType VARCHAR(50)
        DECLARE @DeductibleAmount VARCHAR(50)
        -- Loop thru Policy Level Coverages
        DECLARE POLICY_COVG_CURSOR CURSOR LOCAL FAST_FORWARD FOR
            SELECT s.c.value('(CoverageCode/text())[1]', 'nvarchar(max)') as CoverageCode,
                 s.c.value('(Exposure/text())[1]', 'nvarchar(max)') as Exposure,
                 s.c.value('(DeductibleType/text())[1]', 'nvarchar(max)') as DeductibleType,
                 s.c.value('(DeductibleAmount/text())[1]', 'nvarchar(max)') as DeductibleAmount,
                 s.c.value('(Limit1/text())[1]', 'nvarchar(max)') as Limit1,
                 s.c.value('(Limit2/text())[1]', 'nvarchar(max)') as Limit2,
                 s.c.value('(Limit3/text())[1]', 'nvarchar(max)') as Limit3,
                 s.c.value('(Premium/text())[1]', 'nvarchar(max)') as Premium
            FROM @XML.nodes('ROOT/Policy') AS m(c)
            OUTER APPLY m.c.nodes('Coverages/Coverage') as s(c)
        OPEN POLICY_COVG_CURSOR
        FETCH NEXT FROM POLICY_COVG_CURSOR INTO @CoverageCode, @Exposure, @DeductibleType, @DeductibleAmount, @Limit1, @Limit2, @Limit3, @Premium
        WHILE (@@FETCH_STATUS = 0) BEGIN
            BEGIN TRY

                PRINT 'CovgCd = ' + CONVERT (VARCHAR, @CoverageCode)
                PRINT 'Exposure = ' + CONVERT (VARCHAR, @Exposure)
                PRINT 'DedType = ' + CONVERT (VARCHAR, @DeductibleType)
                PRINT 'DedAmt = ' + CONVERT (VARCHAR, @DeductibleAmount)
                PRINT 'L1 = ' + CONVERT (VARCHAR, @Limit1)
                PRINT 'L2 = ' + CONVERT (VARCHAR, @Limit2)
                PRINT 'L3 = ' + CONVERT (VARCHAR, @Limit3)
                PRINT 'Prem = ' + CONVERT (VARCHAR, @Premium)
            END TRY
            BEGIN CATCH
                PRINT error_message()
            END CATCH
            FETCH NEXT FROM POLICY_COVG_CURSOR INTO @CoverageCode, @Exposure, @DeductibleType, @DeductibleAmount, @Limit1, @Limit2, @Limit3, @Premium
            IF @@FETCH_STATUS = 0
                PRINT '**************************************************'
        END
        CLOSE POLICY_COVG_CURSOR
        DEALLOCATE POLICY_COVG_CURSOR
        --*** END POLICY COVERAGE PROCESSING ***
    END TRY
    BEGIN CATCH
        PRINT error_message()
    END CATCH
    FETCH NEXT FROM POLICY_CURSOR INTO @PolicyNumber, @TransactionType, @EffectiveDate, @ExpirationDate, @ChangeDate, @AccountingDate
    IF @@FETCH_STATUS = 0
        PRINT '--------------------------------------------------'
END
CLOSE POLICY_CURSOR
DEALLOCATE POLICY_CURSOR
--*** END POLICY PROCESSING ***
GO
DROP TABLE XMLwithOpenXML
PRINT '^^^^^^^^^^^ DONE ^^^^^^^^^^^'
Here's the output:
(1 row(s) affected)
Policy = HO00000001
Trans = NB
Eff = 08302018
Exp = 08302019
Chg = 08302018
AcctYr = 20
AcctMo = 08
**************************************************
CovgCd = PL
L1 = 110000
Prem = 11.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110001
L2 = 120002
L3 = 130003
Prem = 34.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 12
--------------------------------------------------
Policy = HO00000001
Trans = AM
Eff = 08302018
Exp = 08302019
Chg = 09302018
AcctYr = 30
AcctMo = 09
**************************************************
CovgCd = PL
L1 = 110000
Prem = 11.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110001
L2 = 120002
L3 = 130003
Prem = 34.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 24
--------------------------------------------------
Policy = HO00000001
Trans = AM
Eff = 08302018
Exp = 08302019
Chg = 09302018
AcctYr = 30
AcctMo = 09
**************************************************
CovgCd = PL
L1 = 110000
Prem = 11.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110001
L2 = 120002
L3 = 130003
Prem = 34.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 36
--------------------------------------------------
Policy = HO00000001
Trans = RN
Eff = 08302019
Exp = 08302020
Chg = 08302018
AcctYr = 20
AcctMo = 08
**************************************************
CovgCd = PL
L1 = 110000
Prem = 11.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110001
L2 = 120002
L3 = 130003
Prem = 34.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
^^^^^^^^^^^ DONE ^^^^^^^^^^^
Thanks.
Dave.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

What is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dave5of5 SSC Journeyman Points: 81 More actions · Answer 2

<Policy> is the parent and <Coverages> are the children.

I'm trying to cursor thru the above XML, processing one <Policy>.</Policy> at a time and insert that data into a table. As you can see, there can be multiple <Policy> nodes with the same data (PolicyNumber, TransactionType, AccountingDate, PolicyEffectiveDate, PolicyExpirationDate, ChangeDate).

What I'm running into is that when I try to process the <Coverages>...</Coverages> for the specific <Policy>.</Policy> that I'm on I end up getting all of the <Coverages>...<Coverages> in the XML.

Any idea on how I can get only those <Coverages>...</Coverages> for the <Policy>.</Policy> that I'm working with?

The desired output is this, and not what's shown above:
Policy = HO00000001
Trans = NB
Eff = 08302018
Exp = 08302019
Chg = 08302018
AcctYr = 20
AcctMo = 08
**************************************************
CovgCd = PL
L1 = 110000
Prem = 11.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 3
--------------------------------------------------
Policy = HO00000001
Trans = AM
Eff = 08302018
Exp = 08302019
Chg = 09302018
AcctYr = 30
AcctMo = 09
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 3
--------------------------------------------------
Policy = HO00000001
Trans = AM
Eff = 08302018
Exp = 08302019
Chg = 09302018
AcctYr = 30
AcctMo = 09
**************************************************
CovgCd = PL
L1 = 110001
L2 = 120002
L3 = 130003
Prem = 34.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 3
--------------------------------------------------
Policy = HO00000001
Trans = RN
Eff = 08302019
Exp = 08302020
Chg = 08302018
AcctYr = 20
AcctMo = 08
**************************************************
CovgCd = PL
L1 = 110000
L2 = 120000
L3 = 130000
Prem = 31.00
**************************************************
CovgCd = MEDPM
L1 = 1000
**************************************************
CovgCd = PI
Prem = 11.00
POLICY COVG COUNT: 3

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

My apologies ... I read your initial post on my phone and missed the important details.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

andycadley SSCertifiable Points: 5296 More actions · Answer 4

I suspect this will be a lot easier and substantially faster if you get rid of all the cursors and just query the XML directly. What are the table structure(s) that you're trying to insert into?

dave5of5 SSC Journeyman Points: 81 More actions · Answer 5

Policy:

Coverage:

You can see that PolicyID is a link to Coverage. So is ProductID, but I have no problem getting that...

andycadley SSCertifiable Points: 5296 More actions · Answer 6

Yeah, so I'd definitely do that with a couple of queries, along the lines of:

Select X.N.value('PolicyNumber[1]','varchar(50)'), X.N.value('AccountingDate[1]','varchar(50)'), X.N.value('PolicyExpirationDate[1]','varchar(50)') From @x.nodes('/ROOT/Policy') X(N)

Select
X.N.value('PolicyNumber[1]','varchar(50)'),
C.N.value('CoverageCode[1]','varchar(50)'),
C.N.value('Premium[1]','varchar(50)')
From @x.nodes('/ROOT/Policy') X(N)
Cross Apply X.N.nodes('Coverages/Coverage') C(N)

I've used an XMLvariable there, but it can just as easily be the table column that has your XML in.