updating rows with xml

  • Hi,

    I have a loop built which selects groups of records out based on different product groups.

    The results are returned as XML which is then updated against the product group in a master product group table.

    This is fine but now the product list is growing fast, the loop loops 100's of times and it's taking minutes to run now.

    is there a way I can write this as a single update?

    declare @products_xml xml

    --get @groupid

    while @group_id is not nul

    begin

    set @products_xml =

    (

    SELECT id, name, price

    FROM product

    WHERE product_group = @group_id

    FOR XML RAW ('Product'), ROOT ('Product_Group');

    )

    update product_group set products_xml = @products_xml

    --get next @groupid

    end

    any help welcome,

    Eamon

  • Without any further info on how you are "getting the next @groupid"....

    update pg

    set pg.products_xml = x.xmldata

    from product_group AS pg

    cross apply (

    SELECT id, name, price

    FROM product

    WHERE product.product_group = pg.product_group

    FOR XML RAW ('Product'), ROOT ('Product_Group')

    ) AS x(xmldata)

    where pg.product_group in (select some_product_groups from somewhere)

    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]

  • I guess my question would be, why are you storing this data as XML to begin with? Why aren't you storing it as properly normalized data?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • because a downstream process then takes the xml which feeds into a report.

    I can't amend the downstream process.

  • EamonSQL (12/1/2013)


    because a downstream process then takes the xml which feeds into a report.

    I can't amend the downstream process.

    Oh my. I feel for you there.

    Did Magoo's answer help you solve your problem?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you mister.magoo, this works perfectly.

    Kind regards,

    Eamon

  • Would it be an option for you to create a view with the xml formatted result set (instead of storing it "physically")?

    Might help to deal with the data in a normalized format while still being able to present it in a xml format.

    This would also help to separate the data storage and the presentation layer. You could change the table structure without the downstream process even taking notice of it (as long as the result set of the view is unchanged).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Viewing 7 posts - 1 through 6 (of 6 total)

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