Update to XML Column

Question

Hi - newbie at XML Coluns in SQL 2008 ...

I have an XML column for a user that looks like this:

<IsirDataValues xmlns="http://schemas.datacontract.org/2004/07/etc" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <ValueList>
    <IsirValueType>
      <IsirCode>AAI</IsirCode>
      <IsirValue />
    </IsirValueType>
    <IsirValueType>
      <IsirCode>ActiveDutyMilitary</IsirCode>
      <IsirValue />
    </IsirValueType>
    <IsirValueType>
      <IsirCode>TransactionReceiptDate</IsirCode>
      <IsirValue>20120215</IsirValue>
    </IsirValueType>
  </ValueList>
</IsirDataValues>

I want to write a SQL statement to update one of the values ... ie: IsirValue for IsirCode of TransactionReceiptDate ...

I have seen some samples on the web but can't get them to work using [columnname].modify(replace value of ...)

Could someone please write a quick update statement for this case for me so I can see what I'm doing wrong??

Thanks!!!!

Answer 1

Try this:

SET NOCOUNT ON

DECLARE @yourTable TABLE ( rowId INT PRIMARY KEY, yourXML XML )

INSERT INTO @yourTable ( rowId, yourXML )
SELECT 1, '<IsirDataValues xmlns="http://schemas.datacontract.org/2004/07/etc" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
   <ValueList>
     <IsirValueType>
       <IsirCode>AAI</IsirCode>
       <IsirValue />
     </IsirValueType>
     <IsirValueType>
       <IsirCode>ActiveDutyMilitary</IsirCode>
       <IsirValue />
     </IsirValueType>
     <IsirValueType>
       <IsirCode>TransactionReceiptDate</IsirCode>
       <IsirValue>20120215</IsirValue>
     </IsirValueType>
   </ValueList>
 </IsirDataValues>'
 
 
DECLARE @newValue VARCHAR(20)

SET @newValue = '20120401'

;WITH XMLNAMESPACES( DEFAULT 'http://schemas.datacontract.org/2004/07/etc' )
SELECT 'before' s, yourXML.query('IsirDataValues/ValueList/IsirValueType[IsirCode[.="TransactionReceiptDate"]]/IsirValue')
FROM @yourTable

;WITH XMLNAMESPACES( DEFAULT 'http://schemas.datacontract.org/2004/07/etc' )
UPDATE @yourTable
SET yourXML.modify('replace value of (IsirDataValues/ValueList/IsirValueType[IsirCode[.="TransactionReceiptDate"]]/IsirValue/text())[1] with sql:variable("@newValue")')
WHERE rowId = 1

;WITH XMLNAMESPACES( DEFAULT 'http://schemas.datacontract.org/2004/07/etc' )
SELECT 'after ' s, yourXML.query('IsirDataValues/ValueList/IsirValueType[IsirCode[.="TransactionReceiptDate"]]/IsirValue')
FROM @yourTable

Answer 2

wBob -

Your code worked perfectly, however, when I try to update back to my original table utilizing either the .modify command with the same code as works for your temp table approach - - or even if i update the temp table first and then do a join from that to my original table and try to set oldfield = newfield, the update simply won't take ...

Maybe my original code wasn't that far off and something else is causing me to bang my head on the wall now after I see your code work fine for an XML.

Any thoughts why an xml data type column in my existing table won't take an update like this????

Thanks!!

Answer 3

It's probably because your actual namespace is different - ie does not contain "etc".  Make sure you adjust these in the query to match exactly.

Alternately it could be to do with the XPath, my example assumes IsirDataValues is the root element.  Is this the case?

If neither of these work, please post back your actual XML.