Update Xml Field with replace value of (XML DML)

Question

I face the situation to update an xml field data with mutliple rows within it.

The XML looks like this ( a cart data containing multiple items )

 <cart>
  <items>
    <ItemId>111613</ItemId>
    <ItemCode>P.KNT330.01.85.1.1.1</ItemCode>
    <ItemName>KANAT330 85 TEAK  PETEK  ODA PVT</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>1</Quantity>
    <MU>AD</MU>
    <Price_Net>143.50</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>143.50</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>110.997250</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>19.97950500</VAT_TL>
    <Price_Gross_TL>130.97675500</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>116950</ItemId>
    <ItemCode>KS.220.S.0850.51.00</ItemCode>
    <ItemName>220 LIK D.TEAK 85 LIK KNT YÖNSÜZ KASA</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>1.000</Quantity>
    <MU>TK</MU>
    <Price_Net>115.20</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>115.20</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>89.107200</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>16.03929600</VAT_TL>
    <Price_Gross_TL>105.14649600</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>112357</ItemId>
    <ItemCode>PRV.071.S.51</ItemCode>
    <ItemName>PERVAZ 70 DÜZ D.TEAK TAKIM</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>1.000</Quantity>
    <MU>TK</MU>
    <Price_Net>45.80</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>45.80</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>35.426300</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>6.37673400</VAT_TL>
    <Price_Gross_TL>41.80303400</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>108561</ItemId>
    <ItemCode>34016-13030</ItemCode>
    <ItemName>IÇ ODA KAPI KILIDI NIKEL  141-45</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>1.000</Quantity>
    <MU>AD</MU>
    <Price_Net>10.35</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>10.35</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>8.005725</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>1.44103050</VAT_TL>
    <Price_Gross_TL>9.44675550</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>108568</ItemId>
    <ItemCode>34026-11160</ItemCode>
    <ItemName>ADOKAPI KÖSE BIRL TKZ TAKIMI</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>1.000</Quantity>
    <MU>AD</MU>
    <Price_Net>1.80</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>1.80</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>1.392300</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>0.25061400</VAT_TL>
    <Price_Gross_TL>1.64291400</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>108543</ItemId>
    <ItemCode>34006-70370</ItemCode>
    <ItemName>ADOKAPI MENTESE  10401.0000.0.2</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>3.000</Quantity>
    <MU>AD</MU>
    <Price_Net>1.30</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>1.30</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>1.005550</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>0.18099900</VAT_TL>
    <Price_Gross_TL>1.18654900</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>108575</ItemId>
    <ItemCode>34026-90300</ItemCode>
    <ItemName>ADOKAPI ROZETI NIKEL 20298.0000.0.2</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>2.000</Quantity>
    <MU>AD</MU>
    <Price_Net>0.45</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>0.45</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>0.348075</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>0.06265350</VAT_TL>
    <Price_Gross_TL>0.41072850</Price_Gross_TL>
    <LineDesc />
  </items>
  <items>
    <ItemId>115240</ItemId>
    <ItemCode>34024-35016</ItemCode>
    <ItemName>TPE ADOKAPI CONTA AÇIK KAHVE</ItemName>
    <THUMBNAIL>/Content/images/noimage.jpg</THUMBNAIL>
    <Quantity>5.000</Quantity>
    <MU>MT</MU>
    <Price_Net>0.36</Price_Net>
    <Currency>TL</Currency>
    <ExchangeRate>1</ExchangeRate>
    <Price_Net_TL>0.36</Price_Net_TL>
    <Item_Disc1_Percent>15</Item_Disc1_Percent>
    <Item_Disc2_Percent>9</Item_Disc2_Percent>
    <Item_Disc3_Percent>0</Item_Disc3_Percent>
    <Item_Disc4_Percent>0</Item_Disc4_Percent>
    <Item_Disc5_Percent>0</Item_Disc5_Percent>
    <Item_Disc6_Percent>0</Item_Disc6_Percent>
    <Price_Net_AfterDisc_TL>0.278460</Price_Net_AfterDisc_TL>
    <VAT_Percent>18.0</VAT_Percent>
    <VAT_TL>0.05012280</VAT_TL>
    <Price_Gross_TL>0.32858280</Price_Gross_TL>
    <LineDesc />
  </items>
  <GenDiscPercent_1 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <GenDiscPercent_2 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <GenDiscPercent_3 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResChar1 />
  <ResChar2>A-0201-1748</ResChar2>
  <ResChar3 />
  <ResNum1 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResNum2 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResNum3 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResNum4 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResDate1>2016-02-18T00:00:00</ResDate1>
  <ResDate2>2016-02-18T00:00:00</ResDate2>
  <ResDate3 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
  <ResDate4 xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:nil="true" />
</cart>

How to modify (let's say) the price of a specific item (with ItemId 116950) in this xml field data?

Answer 1

I assume, that your table contains several rows with the same structure. Do you want to change the price in one row, or - might be dangerous - in all rows?

For this I assume, that you want to change one row. Therefore I'll add a WHERE-clause to the end. If you have to change all, you can change this with CART_DATA.exist(...)=1. This was the fastes XML-based approach to get only rows which really contain the given ItemId.

But: Why is this XML?

The design cries for a classical 1:n-relation via physically defined tables. XML is good for final storage (archives), for interfaces, web-service (data transmission in general), but not for heavily moving data, or data you want to look into with filters or statistical question.

A solution

Nevertheless, this is the code:

Change @YourTable and YourXMLColumn to the actual names.

--Two variables:

DECLARE @itemID INT=116950;
DECLARE @newPrice DECIMAL(10,4)=0.99;

--This will display the price as-is

SELECT t.YourXMLColumn.value('(/cart/items[ItemId=sql:variable("@itemID")]/Price_Net/text())[1]','decimal(10,4)')
FROM @YourTable AS t
WHERE t.ID=1;

--This will modify the price

UPDATE @YourTable
SET YourXMLColumn.modify(N'replace value of (/cart/items[ItemId=sql:variable("@itemID")]/Price_Net/text())[1] with sql:variable("@newPrice")')
WHERE ID=1;

--This will display the new price if the operation was successfull

SELECT t.YourXMLColumn.value('(/cart/items[ItemId=sql:variable("@itemID")]/Price_Net/text())[1]','decimal(10,4)')
FROM @YourTable AS t
WHERE t.ID=1;

Answer 2

Let's say the xml field is called CART_DATA in the table called GM_CART.

We will also use sql-variables as in most cases you will need to run your query with parameters.

DECLARE @cartid int = 1000, @itemid int = 116950, @newprice float = 99.90    
UPDATE GM_CART 
SET CART_DATA.modify('replace value of (/cart/items/Price_Net/text()[.>>(/cart/items/ItemId[.=sql:variable("@itemid")])[1] ])[1]
 with sql:variable("@newprice")')   
    WHERE CARTID=@cartid

to update other nodes if neccesary, you have to call the sql update statement seperately for each one of them