Parsing complex xml

• pamozer

  SSCarpal Tunnel

  Points: 4943

  More actions

  May 31, 2011 at 3:54 pm

  #239136

  I have the following xml

  [/code],

  <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>

  I am trying to grab the image information for this specific offer and I need to bring back the OfferId and the imgUrl and name.

  set nocount on

  declare @feed_rawvarchar(max),

  @feed_xml_idint,

  @bad_charchar(1),

  @feed_subscriptionxml,

  @feed_idint,

  @feed_urlvarchar(500)

  declare @tab_Offer table (

  [offer_Id]int NOT NULL,

  [magazine_id]int NULL,

  [magazine_name]varchar(500) NULL,

  [category_Id]smallint NULL,

  [category_name]nvarchar(256) NULL,

  [source_code]nvarchar(500) NULL,

  [offer_from_date]datetime NOT NULL,

  [offer_to_date]datetime NULL,

  [offer_text]varchar(1000) NULL,

  [welcome_text]varchar(1000) NULL)

  declare @tab_terms table (

  [offer_Id]int NOT NULL,

  [internet_price]decimal(9,2) NULL,

  [news_stand_price]decimal (9,2)NULL,

  [canadian_postage]decimal(9,2) NULL,

  [foreign_postage]decimal (9,2)NULL,

  [issues]tinyint NULL,

  [months]tinyint NOT NULL

  )

  declare @tab_cross_sell table (

  [offer_Id]int NOT NULL,

  [crossCombOfferId] int NULL,

  [magazineId]Int NULL,

  [magazineName]varchar(255) NULL,

  [sourceKey]VARCHAR(255) NULL,

  [offerTerm]TINYINT NULL,

  [OfferValue]DECIMAL(10,2) NULL,

  [canadianPostage]DECIMAL(10,2) NULL,

  [ForeignPostage]DECIMAL(10,2) NULL

  )

  select top 1 @feed_raw =

  fromdbo.subscription_xml_feed with(nolock)

  where[date_processed] is null

  order by [date_inserted] desc

  select @bad_char = substring(@feed_raw, len(@feed_raw), 1)

  if @bad_char not in ('>', char(10), char(13))

  select @feed_subscription = replace(@feed_raw, @bad_char, '')

  else

  select@feed_subscription = @feed_raw

  select

  b.value('(offerId)[1]', 'Int') as [offer_id],

  fr.value('imgUrl)[1]','varchar(128)') as [ImgUrl],

  fr.value('name)[1]', 'varchar(128)') as [ImgName]

  from @feed_subscription.nodes('/Offers/Offer') AS a (b)

  cross Apply b.nodes('OfferImages') as s(fr)

  ')

  I get a syntax error near imgUrl. What am I doing wrong.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  May 31, 2011 at 5:49 pm

  #1332314

  The syntax error is because you have missing brackets before imgUrl and name.

  However, you are also using the wrong path to the image nodes...

  Try this:

  set nocount on

  declare @feed_raw varchar(max),

  @feed_xml_id int,

  @bad_char char(1),

  @feed_subscription xml,

  @feed_id int,

  @feed_url varchar(500)

  declare @tab_Offer table (

  [offer_Id] int NOT NULL,

  [magazine_id] int NULL,

  [magazine_name] varchar(500) NULL,

  [category_Id] smallint NULL,

  [category_name] nvarchar(256) NULL,

  [source_code] nvarchar(500) NULL,

  [offer_from_date] datetime NOT NULL,

  [offer_to_date] datetime NULL,

  [offer_text] varchar(1000) NULL,

  [welcome_text] varchar(1000) NULL)

  declare @tab_terms table (

  [offer_Id] int NOT NULL,

  [internet_price] decimal(9,2) NULL,

  [news_stand_price] decimal (9,2)NULL,

  [canadian_postage] decimal(9,2) NULL,

  [foreign_postage] decimal (9,2)NULL,

  [issues] tinyint NULL,

  [months] tinyint NOT NULL

  )

  declare @tab_cross_sell table (

  [offer_Id] int NOT NULL,

  [crossCombOfferId] int NULL,

  [magazineId] Int NULL,

  [magazineName] varchar(255) NULL,

  [sourceKey] VARCHAR(255) NULL,

  [offerTerm] TINYINT NULL,

  [OfferValue] DECIMAL(10,2) NULL,

  [canadianPostage] DECIMAL(10,2) NULL,

  [ForeignPostage] DECIMAL(10,2) NULL

  )

  select @feed_raw = '<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 @bad_char = substring(@feed_raw, len(@feed_raw), 1)

  if @bad_char not in ('>', char(10), char(13))

  select @feed_subscription = replace(@feed_raw, @bad_char, '')

  else

  select @feed_subscription = @feed_raw

  select

  b.value('(offerId)[1]', 'Int') as [offer_id],

  fr.value('(@imgUrl)[1]','varchar(128)') as [ImgUrl],

  fr.value('(@name)[1]', 'varchar(128)') as [ImgName]

  from @feed_subscription.nodes('Offer') AS a (b)

  cross Apply b.nodes('OfferImages/image') as s(fr)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]