July 27, 2012 at 11:31 pm
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
July 30, 2012 at 10:18 am
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?
July 31, 2012 at 5:50 pm
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