What is the best way to iterate XML elements in SQL?

  • I'm having problems trying to determine the best way to iterate through multiple instances of an element so that I can do processing on subelements and attributes. Below is a sample of the XML format I am dealing with:

    <output xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PublishPricingRule.xsd" scenario="PublishPricingRule" sourceSystem="Pricing" eventType="Persist" transactionID="2012-06-26T13:46:52.064">

    <pricingRuleCollection>

    <pricingRule >

    <pricingInstruction precedenceIndex="1314">

    <hierarchyNodeInclusionQualifier precedenceIndex="1315">

    <includedHierarchyNodes />

    </hierarchyNodeInclusionQualifier>

    <hierarchyNodeExclusionQualifier precedenceIndex="1316">

    <excludedHierarchyNodes />

    </hierarchyNodeExclusionQualifier>

    <mediaInclusionQualifier />

    <mediaExclusionQualifier />

    <brandInclusionQualifier precedenceIndex="1317" />

    <brandExclusionQualifier precedenceIndex="1318" />

    <monetaryHurdleRangeQualifier precedenceIndex="1319">

    <range lower="0.0" upper="9999.99" />

    </monetaryHurdleRangeQualifier>

    <shippingServiceInclusionQualifier precedenceIndex="1320">

    <qualifyingShippingServices>

    <qualifyingShippingService value="APO/FPO-Std" />

    <qualifyingShippingService value="Catalog Desk Continental" />

    <qualifyingShippingService value="Continental-Std" />

    </qualifyingShippingServices>

    </shippingServiceInclusionQualifier>

    <creditCardInclusionQualifier precedenceIndex="1321">

    <qualifyingCreditCards>

    <qualifyingCreditCard name="Amex" />

    <qualifyingCreditCard name="Visa" />

    </qualifyingCreditCards>

    </creditCardInclusionQualifier>

    <directShipInclusionQualifier precedenceIndex="1322" />

    <dropShipInclusionQualifier precedenceIndex="1323" />

    <proportionalShippingFeeAdjuster adjustmentAmount="0.00" />

    </pricingInstruction>

    </pricingRule>

    </pricingRuleCollection>

    </output>

    I'm trying to iterate through the pricingInstruction element so that I can populate a group of tables and include a unique pricingInstruction ID. Each of the subelements can potentially have multiple subelements as well, as in the case of shippingServiceInclusionQualifier. So I will need to iterate through those as well. Here is a sample of the code that was used to do this. I need to know the best way to translate this to only SQL so I can move the logic to a stored procedure.

    InputRoot.MRM.pricingRuleCollection.pricingRule.pricingInstruction

    SETcountTiers = CARDINALITY(InputRoot.MRM.pricingRuleCollection.pricingRule.pricingInstruction[]);

    SET I = 0;

    SET J = 1;

    DECLARE strField character;

    WHILE I < countTiers DO

    SET I = I + 1 ;

    SET J = 1 ;

    SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier.Tier = I ;

    -- Map the Associate Exclution Qualifiers

    SET strField = FIELDNAME(InputRoot.MRM.pricingRuleCollection.pricingRule.pricingInstruction.associateExclusionQualifier) ;

    IF( strField IS NOT NULL) THEN

    SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier.CouponUsage[J].Usage = 1 ;

    SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier.CouponUsage[J].UsageType = 1 ;

    SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier.CouponUsage[J].UsageValue = ''; -- Specify a blank value, if UsageValue not available for the UsageType

    SET J = J + 1 ;

    END IF;

    Note: Not that it matters, but the sample code is creating an XML output file whereas I will be inserting rows into a temp table.

    Thanks

  • You're going to want to use a combination of CROSS (or OUTER) APPLY to turn the multiple nodes inside of some of the hierarchies into a virtual table of sorts. Note - you want to structure the APPLY statement to land at the "top" of the subtrees you plan on iterating through.

    Something like the follow (note I added a few ID columns for testing purposes):

    declare @xml XML

    set @xml='<output xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PublishPricingRule.xsd" scenario="PublishPricingRule" sourceSystem="Pricing" eventType="Persist" transactionID="2012-06-26T13:46:52.064">

    <pricingRuleCollection id="1">

    <pricingRule id="3" >

    <pricingInstruction precedenceIndex="1314">

    <hierarchyNodeInclusionQualifier precedenceIndex="1315">

    <includedHierarchyNodes />

    </hierarchyNodeInclusionQualifier>

    <hierarchyNodeExclusionQualifier precedenceIndex="1316">

    <excludedHierarchyNodes />

    </hierarchyNodeExclusionQualifier>

    <mediaInclusionQualifier />

    <mediaExclusionQualifier />

    <brandInclusionQualifier precedenceIndex="1317" />

    <brandExclusionQualifier precedenceIndex="1318" />

    <monetaryHurdleRangeQualifier precedenceIndex="1319">

    <range lower="0.0" upper="9999.99" />

    </monetaryHurdleRangeQualifier>

    <shippingServiceInclusionQualifier precedenceIndex="1320">

    <qualifyingShippingServices>

    <qualifyingShippingService value="APO/FPO-Std" />

    <qualifyingShippingService value="Catalog Desk Continental" />

    <qualifyingShippingService value="Continental-Std" />

    </qualifyingShippingServices>

    </shippingServiceInclusionQualifier>

    <creditCardInclusionQualifier precedenceIndex="1321">

    <qualifyingCreditCards>

    <qualifyingCreditCard name="Amex" />

    <qualifyingCreditCard name="Visa" />

    </qualifyingCreditCards>

    </creditCardInclusionQualifier>

    <directShipInclusionQualifier precedenceIndex="1322" />

    <dropShipInclusionQualifier precedenceIndex="1323" />

    <proportionalShippingFeeAdjuster adjustmentAmount="0.00" />

    </pricingInstruction>

    </pricingRule>

    </pricingRuleCollection>

    </output>'

    select @xml

    select r.value('(/output/@scenario)[1]','varchar(250)'),

    r.value('(/output/pricingRuleCollection/@id)[1]','varchar(250)'),

    r.value('(/output/pricingRuleCollection/pricingRule/@id)[1]','varchar(250)'),

    r.value('(/output/pricingRuleCollection/pricingRule/pricingInstruction/@precedenceIndex)[1]','int')

    ,s.value('@value','varchar(100)')

    from @xml.nodes('/') x(r)

    cross apply r.nodes('output/pricingRuleCollection/pricingRule/pricingInstruction') l(h)

    cross apply h.nodes('shippingServiceInclusionQualifier/qualifyingShippingServices/qualifyingShippingService') o(s)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Maybe it would be nice to add something like

    Id = DENSE_RANK()() OVER (ORDER BY s.value('@PrecedenceIndex','int') )

Viewing 3 posts - 1 through 2 (of 2 total)

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