updating rows with xml

Question

Post reply

updating rows with xml

EamonSQL

SSCrazy

Points: 2307
More actions
November 30, 2013 at 11:47 am

#97167

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

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)

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

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

I can't amend the downstream process.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

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)

EamonSQL SSCrazy Points: 2307 More actions · Answer 5

Thank you mister.magoo, this works perfectly.

Kind regards,

Eamon

LutzM SSC Guru Points: 107049 More actions · Answer 6

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]