none
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!!!!

    Friday, March 02, 2012 10:56 PM

Answers

  • 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

    • Proposed as answer by LMU92 Saturday, March 03, 2012 4:03 PM
    • Marked as answer by KJian_ Friday, March 09, 2012 3:08 AM
    Friday, March 02, 2012 11:59 PM
    Answerer

All replies

  • 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

    • Proposed as answer by LMU92 Saturday, March 03, 2012 4:03 PM
    • Marked as answer by KJian_ Friday, March 09, 2012 3:08 AM
    Friday, March 02, 2012 11:59 PM
    Answerer
  • 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!!


    • Edited by Scott Poe Monday, March 05, 2012 10:25 PM
    Monday, March 05, 2012 10:24 PM
  • 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.

    Monday, March 05, 2012 10:35 PM
    Answerer