Account Account February 2016

How can I update an xml node than contains another node with a specific value XML in sql server

Example

declare @x xml;

set @x = N'
<Root>
  <Book>
     <ID>1</ID>
     <Price>100</Price>
     <Title>false</Title>
  </Book>
  <Book>
        <ID>2</ID>
        <Price>200</Price>
        <Price>false</Price>
  </Book>
  <Book>
        <ID>3</ID>
        <Price>300</Price>
        <Title>false</Title>
   </Book>
   <Book>
        <ID>4</ID>
        <Price>400</Price>
        <Title>false</Title>
   </Book>
</Root>'

Query to select the node that contains a child node with id =3

SELECT
@tmp = a.b.query('../.') 
from @x.nodes('//Book/ID[text()="3"]') a(b)

How can i modify the Price value of the selected node?

Answers


Parfait February 2016

Consider using SQL Server's XML Data Modification Language (DML), specifically the replace value of method. Below example updates Price to 9999:

declare @x xml;

set @x = N'
<Root>
  <Book>
     <ID>1</ID>
     <Price>100</Price>
     <Title>false</Title>
  </Book>
  <Book>
        <ID>2</ID>
        <Price>200</Price>
        <Price>false</Price>
  </Book>
  <Book>
        <ID>3</ID>
        <Price>300</Price>
        <Title>false</Title>
   </Book>
   <Book>
        <ID>4</ID>
        <Price>400</Price>
        <Title>false</Title>
   </Book>
</Root>'

set @x.modify('replace value of (/Root/Book[ID="3"]/Price/text())[1] with ("9999")')

select @x

Post Status

Asked in February 2016
Viewed 3,006 times
Voted 8
Answered 1 times

Search




Leave an answer