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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy