March 3, 2011 at 3:14 pm
I am trying to dynamically present a collection of nodes in a multi-level XML doc.
Here is the code I have so far...feeding the fr node into an OUTER APPLY to read into the termConditions node to pickup the first and second termCondition in the group. Do you know of a way to make it dynamic?
What I want is for new columns to be added to the resultset depending on how many termCondition nodes exist within the termConditions node without having to specify the [font="Courier New"].value[/font] function with [font="Courier New"][n][/font] for each item since the max number of items may be unknown and/or unbounded.
DECLARE @feed_subscription XML = N'<Offers>
<Offer>
<magazineId>92</magazineId>
<magazineName>4-Wheel Off-Road</magazineName>
<offerId>3958</offerId>
<offerText></offerText>
<welcomeText>As the world''s largest truck enthusiast, Source Interlink Media''s Truck Off Road Magazines are your best direct connection to the automotive industry''s number one segment - the light truck marketplace. Click on a title for more information.</welcomeText>
<storeCategoryId>57</storeCategoryId>
<storeCategoryName>Truck Off Road</storeCategoryName>
<sourceCode>I8FFNE</sourceCode>
<offerFromDate>2008-06-16 00:00:00.0</offerFromDate>
<offerToDate>2013-06-16 00:00:00.0</offerToDate>
<PaymentTypes>
<paymentType code="American Express" id="1"/>
<paymentType code="MasterCard" id="2"/>
<paymentType code="Visa" id="3"/>
<paymentType code="Discover" id="4"/>
</PaymentTypes>
<offerTerms>
<term>
<newStandPrice>71.88</newStandPrice>
<internetPrice>12.00</internetPrice>
<canadianPostage>12.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
<issues>12</issues>
<months>12</months>
<termConditions>
<termCondition>condition 3958.1</termCondition>
<termCondition>condition 3958.2</termCondition>
</termConditions>
</term>
<term>
<newStandPrice>143.76</newStandPrice>
<internetPrice>18.00</internetPrice>
<canadianPostage>24.00</canadianPostage>
<foreignPostage>48.00</foreignPostage>
<issues>24</issues>
<months>24</months>
</term>
</offerTerms>
<OfferImages>
<image imgUrl="https://www.circsource.com/images/mags/4wheeloffroad/osc_coverimage0_140h.jpg" name="osc_coverimage0_140h"/>
<image imgUrl="https://www.circsource.com/images/mags/4wheeloffroad/osc_coverimage0_150w.jpg" name="osc_coverimage0_150w"/>
<image imgUrl="https://www.circsource.com/images/mags/4wheeloffroad/osc_coverimage0_400h.jpg" name="osc_coverimage0_400h"/>
</OfferImages>
</Offer>
<Offer>
<magazineId>239</magazineId>
<magazineName>Motor Trend Classic</magazineName>
<offerId>3956</offerId>
<offerText></offerText>
<welcomeText>SOURCE INTERLINK''s Premier Automotive magazines cover new and classic vehicles. Read about trends, news, reviews and road tests and more of all your favorite vehicles.</welcomeText>
<storeCategoryId>59</storeCategoryId>
<storeCategoryName>Premier Automotive</storeCategoryName>
<sourceCode>wwwwww</sourceCode>
<offerFromDate>2010-11-03 00:00:00.0</offerFromDate>
<offerToDate>2015-11-03 00:00:00.0</offerToDate>
<PaymentTypes>
<paymentType code="American Express" id="1"/>
<paymentType code="MasterCard" id="2"/>
<paymentType code="Visa" id="3"/>
<paymentType code="Discover" id="4"/>
<paymentType code="Bill Me Later" id="6"/>
</PaymentTypes>
<offerTerms>
<term>
<internetPrice>59.95</internetPrice>
<newStandPrice>59.96</newStandPrice>
<canadianPostage>24.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
<issues>4</issues>
<months>12</months>
<termConditions>
<termCondition>condition 3956.1</termCondition>
<termCondition>condition 3956.2</termCondition>
</termConditions>
</term>
<term>
<newStandPrice>14.99</newStandPrice>
<internetPrice>14.99</internetPrice>
<canadianPostage>6.00</canadianPostage>
<foreignPostage>6.00</foreignPostage>
<issues>1</issues>
<months>1</months>
</term>
</offerTerms>
<OfferImages>
<image imgUrl="https://www.circsource.com/images/mags/motortrendclassic/osc_coverimage0_140h.jpg" name="osc_coverimage0_140h"/>
<image imgUrl="https://www.circsource.com/images/mags/motortrendclassic/osc_coverimage0_150w.jpg" name="osc_coverimage0_150w"/>
<image imgUrl="https://www.circsource.com/images/mags/motortrendclassic/osc_coverimage0_400h.jpg" name="osc_coverimage0_400h"/>
</OfferImages>
<CrossMarketingOffers>
<crossSells>
<CrossSell>
<crossCombOfferId>138</crossCombOfferId>
<magazineId>63</magazineId>
<magazineName>European Car</magazineName>
<sourceKey>i8ex12</sourceKey>
<offerTerm>12</offerTerm>
<offerValue>11.97</offerValue>
<canadianPostage>12.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
</CrossSell>
<CrossSell>
<crossCombOfferId>84</crossCombOfferId>
<magazineId>104</magazineId>
<magazineName>Hot Rod</magazineName>
<sourceKey>i8ex14</sourceKey>
<offerTerm>12</offerTerm>
<offerValue>14.00</offerValue>
<canadianPostage>12.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
</CrossSell>
<CrossSell>
<crossCombOfferId>145</crossCombOfferId>
<magazineId>3</magazineId>
<magazineName>Automobile</magazineName>
<sourceKey>I8FX07</sourceKey>
<offerTerm>12</offerTerm>
<offerValue>7.00</offerValue>
<canadianPostage>12.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
</CrossSell>
</crossSells>
</CrossMarketingOffers>
</Offer>
</Offers>' ;
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],
fr.value('(internetPrice)[1]', 'money') AS [internet_price],
fr.value('(newStandPrice)[1]', 'money') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'money') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'money') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months],
tcs.value('(termCondition)[1]', 'varchar(100)') AS [termCondition1],
tcs.value('(termCondition)[2]', 'varchar(100)') AS [termCondition2]
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('offerTerms/term') AS s (fr)
OUTER APPLY fr.nodes('termConditions') AS t (tcs) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 3:25 pm
I would shred it in a normalized staging table together with a row number per termCondition and use that staging table to perform a CrossTab (or DynamicCrossTab, if needed).
Edit: here's the code to shred the data (forgot to post it...)
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],
fr.value('(internetPrice)[1]', 'money') AS [internet_price],
fr.value('(newStandPrice)[1]', 'money') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'money') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'money') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months],
tcs.value('.[1]', 'varchar(100)') AS termCondition,
ROW_NUMBER() OVER(PARTITION BY b.value('(offerId)[1]', 'Int') ORDER BY (SELECT 1) ) as row
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('offerTerms/term') AS s (fr)
CROSS APPLY fr.nodes('termConditions/termCondition') AS t (tcs) ;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply