March 3, 2011 at 10:45 am
I have the following XML that I need to shred into multiple tables. Also is the query I am trying to apply. The problem is that I need to Include the OfferId In each of the tables and the commonality. When I try to use the cross apply method I get back way to many rows for one offerId. I am getting back about 139 rows for one offerID when there are only two sets of terms related to this one offerId.
Any help would be appreciated
<Offers>
<Offer>
<magazineId>92</magazineId>
<magazineName>4-Wheel & Off-Road</magazineName>
<offerId>3958</offerId>
<offerText><b><i>4-Wheel & Off-Road</i></b> is the world's largest truck enthusiast magazine!
<br>
<ul>
<li>Exclusive Scoops on New Products</li>
<li>Product Evaluations and Trail Destinations</li>
<li>Tech Articles such as Suspension Modifications & Engine Buildups</li>
</ul>
Don't miss a single issue - <b>Subscribe Now!</b></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>
</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>
<newStandPrice>59.96</newStandPrice>
<internetPrice>59.95</internetPrice>
<canadianPostage>24.00</canadianPostage>
<foreignPostage>24.00</foreignPostage>
<issues>4</issues>
<months>12</months>
</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>
select
b.value('(offerId)[1]', 'Int') as [offer_id],
fr.value('(internetPrice)[1]', 'decimal') as [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') as [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') as [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') as [foreign_postage],
fr.value('(issues)[1]', 'tinyint') as [issues],
fr.value('(months)[1]', 'tinyint') as [months]
from @feed_subscription.nodes('/Offers/Offer') AS a(b)
cross Apply b.nodes('/Offers/Offer/offerTerms/term') as s(fr)
Here is the table definition for where the xml is being extracted to for the above query
CREATE TABLE [dbo].[terms_working](
[newstandPrice] [decimal](10, 2) NULL,
[internetPrice] [decimal](10, 2) NULL,
[canadianPostage] [decimal](10, 2) NULL,
[foreignPostage] [decimal](10, 2) NULL,
[issues] [tinyint] NULL,
[months] [tinyint] NULL,
[offer_id] [int] NULL
) ON [PRIMARY]
GO
March 3, 2011 at 11:01 am
Try this:
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],
fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months]
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('/Offers/Offer[1]/offerTerms/term') AS s (fr) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 12:06 pm
Perfect. Thank you.
March 3, 2011 at 1:47 pm
You're shredding the same document twice when you really don't need to, because you started from the highest relevant point in the document hierarchy and worked down, when you really should start from the lowest relevant point in the hierarchy and work up. You don't need the cross apply at all.
SELECT fr.value('(../../offerId)[1]', 'int') AS offer_id,
fr.value('(internetPrice)[1]', 'decimal') as [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') as [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') as [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') as [foreign_postage],
fr.value('(issues)[1]', 'tinyint') as [issues],
fr.value('(months)[1]', 'tinyint') as [months]
FROM @feed_subscription.nodes('/Offers/Offer/offerTerms/term') AS s(fr)
If you do want to use the cross apply then you should use a relative path not an absolute path.
select
b.value('(offerId)[1]', 'Int') as [offer_id],
fr.value('(internetPrice)[1]', 'decimal') as [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') as [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') as [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') as [foreign_postage],
fr.value('(issues)[1]', 'tinyint') as [issues],
fr.value('(months)[1]', 'tinyint') as [months]
from @feed_subscription.nodes('/Offers/Offer') AS a(b)
cross Apply b.nodes('offerTerms/term') as s(fr) -- relative path
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2011 at 2:04 pm
Thanks, nice tip 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 2:35 pm
opc.three (3/3/2011)
Try this:
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months]
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('/Offers/Offer[1]/offerTerms/term') AS s (fr) ;
This returns incorrect results. It always returns the terms for the first offer rather than the terms that correspond to the current offer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2011 at 2:37 pm
Thank you. I actually like the version not using cross apply.
March 3, 2011 at 2:42 pm
pamozer (3/3/2011)
Thank you. I actually like the version not using cross apply.
... until you test both versions against a larger XML file...
Your preference may change 😉
March 3, 2011 at 2:45 pm
drew.allen (3/3/2011)
opc.three (3/3/2011)
Try this:
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months]
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('/Offers/Offer[1]/offerTerms/term') AS s (fr) ;
This returns incorrect results. It always returns the terms for the first offer rather than the terms that correspond to the current offer.
Drew
The OP seemed to like it 😀 Still learning here...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 2:53 pm
opc.three (3/3/2011)
drew.allen (3/3/2011)
opc.three (3/3/2011)
Try this:
SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],
fr.value('(newStandPrice)[1]', 'decimal') AS [news_stand_price],
fr.value('(canadianPostage)[1]', 'decimal') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal') AS [foreign_postage],
fr.value('(issues)[1]', 'tinyint') AS [issues],
fr.value('(months)[1]', 'tinyint') AS [months]
FROM @feed_subscription.nodes('/Offers/Offer') AS a (b)
CROSS APPLY b.nodes('/Offers/Offer[1]/offerTerms/term') AS s (fr) ;
This returns incorrect results. It always returns the terms for the first offer rather than the terms that correspond to the current offer.
Drew
The OP seemed to like it 😀 Still learning here...
The OP probably didn't look closely enough at the results to notice.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2011 at 2:59 pm
I'm assuming the OP is me but what does it stand for? And I hadn't checked the actual data yet only the counts.
March 3, 2011 at 3:08 pm
pamozer (3/3/2011)
I'm assuming the OP is me but what does it stand for? And I hadn't checked the actual data yet only the counts.
OP is Original Poster.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 3, 2011 at 3:17 pm
drew.allen (3/3/2011)
The OP probably didn't look closely enough at the results to notice.
Sure, nor did I obviously 😀
I am trying to further this example to learn how to present multi-level XML structures using APPLY. I created a new topic here http://www.sqlservercentral.com/Forums/Topic1072972-338-1.aspx since it seemed different enough and I have already caused enough trouble on this thread. Any help would be much appreciated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 4, 2011 at 9:59 am
So I am obviously not understanding something, I tried to apply the same logic to this query and I am getting null back for the OfferId. What am I missing?
SELECT distinct fr.value('(../../offerId)[1]', 'int') AS offer_id,
fr.value('(crossCombOfferId)[1]', 'tinyint') AS [cross_combo_offer_id],
fr.value('(magazineId)[1]', 'smallint') AS [magazine_id],
fr.value('(magazineName)[1]', 'nvarchar(500)') AS [magazine],
fr.value('(sourceKey)[1]','varchar(255)') AS [source_key],
fr.value('(offerTerm)[1]', 'tinyint') AS [offer_term],
fr.value('(offerValue)[1]', 'decimal(9,2)') AS [offer_value],
fr.value('(canadianPostage)[1]', 'decimal (9,2)') AS [canadian_postage],
fr.value('(foreignPostage)[1]', 'decimal (9,2)') AS [foreign_postage]
FROM @feed_subscription.nodes('/Offers/Offer/CrossMarketingOffers/crossSells/CrossSell') AS s (fr);
March 4, 2011 at 10:39 am
I was able to get it to work using the cross apply method
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply