Post reply

Parsing complex xml

  • 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_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 top 1 @feed_raw =

    from dbo.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.

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

  • May 31, 2011 at 5:54 pm

    #1332316

    thank you. the @ sign was the other piece I was missing.

    • Viewing 3 posts - 1 through 2 (of 2 total)

    You must be logged in to reply to this topic. Login to reply