Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is the best way to iterate XML elements in SQL? Expand / Collapse
Author
Message
Posted Friday, July 27, 2012 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 31, 2012 7:54 PM
Points: 1, Visits: 13
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
SET countTiers = 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[I].Tier = I ;

-- Map the Associate Exclution Qualifiers
SET strField = FIELDNAME(InputRoot.MRM.pricingRuleCollection.pricingRule.pricingInstruction[I].associateExclusionQualifier) ;
IF( strField IS NOT NULL) THEN
SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier[I].CouponUsage[J].Usage = 1 ;
SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier[I].CouponUsage[J].UsageType = 1 ;
SET OutputRoot.MRM.Coupon.CouponTiers.CouponTier[I].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
Post #1336869
Posted Monday, July 30, 2012 10:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 7,135, Visits: 15,142
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?
Post #1337380
Posted Tuesday, July 31, 2012 5:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 4, 2014 1:50 PM
Points: 339, Visits: 386
Maybe it would be nice to add something like

Id = DENSE_RANK()() OVER (ORDER BY s.value('@PrecedenceIndex','int') )
Post #1338253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse