October 26, 2012 at 12:27 pm
I need to update table Cart.Storage which has XML. In particular the <Removable> needs updating on any <CartItem> that matches <Plan> and <Rule> criteria; several <CartItem> will be updated, and others that won't. Here is sample xml from the Storage column:
<Cart xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="CartDetail">
<Items>
<CartItem xsi:type="CartItemDetail">
<otherfields />
<Plan>DefaultAddRow</Plan>
<Rule>_</Rule>
<Removable>false</Removable>
</CartItem>
</Items>
</Cart>
Here is query that returns error "Invalid object name 'item'".
DECLARE @removable BIT = 1
UPDATE item
SET item.modify('replace value of (Removable)[1] with sql:variable("@removable")')
FROM SalesOrder.Cart c
CROSS APPLY c.Storage.nodes('Cart/Items/CartItem') as R(item)
where
item.value('Plan[1]', 'nvarchar(50)') = 'DefaultAddRow'
and item.value('Rule[1]', 'nvarchar(50)') = '_'
and item.value('Removable[1]', 'bit') = 0
Thanks.
October 26, 2012 at 1:34 pm
Items is a context node created from nodes. The update statement has to work with the XML column directly. And because of that you have to move your where clause into the XML part of your update statement.
update SalesOrder.Cart
set Storage.modify('replace value of
(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"]/Removable/text())[1]
with sql:variable("@removable")')
October 26, 2012 at 2:04 pm
Great, thanks! That works for the first CartItem... it needs to look at all CartItems, some of which need to be updated, some don't. the [1] only updates the first, [2] second etc.
October 26, 2012 at 2:22 pm
It is only possible to target one value in the XML with the update. You have to put your update statement in a loop that execute the update statement as long as it is necessary.
while exists (select *
from SalesOrder.Cart
where Storage.exist('(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"])') = 1)
begin
update SalesOrder.Cart
set Storage.modify('replace value of
(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"]/Removable/text())[1]
with sql:variable("@removable")')
where Storage.exist('(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"])') = 1
end
October 26, 2012 at 2:33 pm
Awesome, thanks! One last wrinkle: The existing Removable is sometimes null... I need to update when Removable is null or false...
[Plan = "DefaultAddRow" and
Rule = "_" and
(Removable = "false" or Removable = null)]
Correct?
October 26, 2012 at 3:24 pm
Do you mean that the value actually is null or that the node Removable is missing?
October 26, 2012 at 3:27 pm
Looks like it's missing... I assume in that case it would need to be added.
October 26, 2012 at 3:32 pm
Then you need two while loops. One that adds the missing elements and one that updates the ones that need updating.
while exists(select *
from SalesOrder.Cart
where Storage.exist('Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
count(Removable) = 0]') = 1)
begin
update SalesOrder.Cart
set Storage.modify('insert <Removable>{sql:variable("@removable")}</Removable> as last into
(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
count(Removable) = 0])[1]')
where Storage.exist('Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
count(Removable) = 0]') = 1
end
while exists (select *
from SalesOrder.Cart
where Storage.exist('(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"])') = 1)
begin
update SalesOrder.Cart
set Storage.modify('replace value of
(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"]/Removable/text())[1]
with sql:variable("@removable")')
where Storage.exist('(Cart/Items/CartItem[Plan = "DefaultAddRow" and
Rule = "_" and
Removable = "false"])') = 1
end
October 26, 2012 at 3:37 pm
Awesome, thank you very much! A million +1's for you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply