Search code examples
xmlsql-server-2008sqlxml

SQL Server 2008 XML update in a Fragment?U

I have a SQL Server 2008 table with 50k rows, each with an XML fragment in a varchar column that looks like this:

<infoElems>
        <infoElem id="1" Name="somename" money="3399.3984939" />
</infoElems>

I need to select out the varchar column, select out the money attribute, change it to an actual money type (3399.40 in my example), and then put the whole fragment back.

Can anyone point how to get me through this? I think I need to create an XML index of some sort? Confused.

Thanks.

Solution

  • The datatypes involved make this ugly, especially if the XML in your columns varies other than the price. Here is something that will work in SQL 2008. If you have to update lots of rows you will have to use this with a CURSOR.

    DECLARE @orig VARCHAR(20), @new money
DECLARE @origXml XML, @newXml VARCHAR(100)

// first you have to cast to xml
SELECT @origXml = CAST(myColunm AS XML) 
FROM dbo.Tbl1
WHERE ...

// then extract the value as a string
SET @orig = @origXml.value('(//infoElem/@money)[1]','varchar(20)') 

// then get the new value to the right percision - MONEY is accurate to the ten-thousandth hence the ROUND
SET @new = ROUND(CAST(@orig AS MONEY),2)  

SET @newXml = REPLACE(CAST(@origXml AS VARCHAR(100)),  
  'money="'+CAST(@orig AS VARCHAR)+'"',
  'money="'+CAST(@new AS VARCHAR)+'"') // then replace - this can be combined with the update 

UPDATE dbo.Tbl1 SET myColunm = @newXml // then update

    Depending on your situation, it may be easier to just write an external script to do this. You could also look into using regex - see this post, but that could get really ugly.