1

I have an XML column that needs to be updated multiple time for the same ID. When I run the query it updates the XML only for the first matching ID. Here is the query

Update  Quote 
Set     [XML].modify('replace value of (/Document/LineItems/LineItem[@ID=sql:column("QLines")]/LineItemStatus/text())[1] with "DELETED"')
From    Quote Qt
    Inner Join @QuoteLines Q on Qt.QuoteID=Q.QtID

@QuoteLines Table
QLines  QtID
30  111645001
40  111645001

Quote Table
QuoteID         XML
111645001   '<Document ID="111645001">'

In Quote Table the XML status should be "Deleted" for both lineitem 30 & 40 but it is updating it only for lineitem 30.

I don't have to use Cursors or something like iterations. Thanks

Habib
  • 300
  • 2
  • 14
  • 1
    In future questions, please format your code as code, either by indenting 4 spaces, or highlighting it and clicking the code button (above the question's text area). – yoozer8 Apr 13 '12 at 19:07
  • http://stackoverflow.com/questions/1232504/how-to-modify-multiple-nodes-using-sql-xquery-in-ms-sql-2005 – sisdog Apr 15 '12 at 20:18

0 Answers0