SQL Update Part of XML string in a column

Question

Table a:

+------+------+------+--------------------------------------+
| col1 | col2 | col3 | Col4                                 |
+------+------+------+--------------------------------------+
|    1 | A    | E    |<?xml version .....<v> "OOOO"</v></vs>|  
|    2 | B    | F    |<?xml version .....<v> "KKKK"</v></vs>|      
|    3 | C    | G    |<?xml version .....<v> "LLLL"</v></vs>|   
|    4 | B    | E    |<?xml version .....<v> "MMMM"</v></vs>|     
|    5 | T    | G    |<?xml version .....<v> "NNNN"</v></vs>| 
+------+------+------+--------------------------------------+

How would I update the xml between the ""?

Below is my attempt, which results in updating col4 to NewValue 1 - x. Basically it doesn't keep the xml format.

UPDATE sf
SET sf.col4 = 
 (CASE col4
        WHEN '%OOOO%'
        THEN '%NewValue1%'
        WHEN '%KKKK%'
        THEN '%NewValue2%'
        WHEN '%LLLL%'
        THEN '%NewValue3%'
        WHEN '%MMMM%'
        THEN '%NewValue4%'
        WHEN '%NNNN%'
        THEN '%NewValue5%'
        END)

Expected Result:

+------+------+------+-------------------------------------------+
| col1 | col2 | col3 | Col4                                      |
+------+------+------+-------------------------------------------+
|    1 | A    | E    |<?xml version .....<v> "NewValue1"</v></vs>|  
|    2 | B    | F    |<?xml version .....<v> "NewValue2"</v></vs>|      
|    3 | C    | G    |<?xml version .....<v> "NewValue3"</v></vs>|   
|    4 | B    | E    |<?xml version .....<v> "NewValue4"</v></vs>|     
|    5 | T    | G    |<?xml version .....<v> "NewValue5"</v></vs>| 
+------+------+------+-------------------------------------------+

Col4 is data type : nvarchar(MAX)

What is the datatype for 'col4' and what is the expected result? — yuvi
– yuvi, Commented Feb 2, 2016 at 5:34
@RajaYuvi I expect only the values between " " in the xml col4 to update. Such as <?xml......"NewValue1"...</vs> instead of <?xml......"OOOO"...</vs> — Alkippe Nikephoros
– Alkippe Nikephoros, Commented Feb 2, 2016 at 6:02

Pரதீப் · Accepted Answer · 2016-02-02 06:14:51Z

3

Use Replace

Can u try this:-

UPDATE sf
SET    sf.col4 = ( CASE 
                     WHEN col4 like '%OOOO%' THEN Replace(Col4, 'OOOO', 'NewValue1')
                     WHEN col4 like '%KKKK%' THEN Replace(Col4, 'KKKK', 'NewValue2')
                     WHEN col4 like '%LLLL%' THEN Replace(Col4, 'LLLL', 'NewValue3')
                     WHEN col4 like '%MMMM%' THEN Replace(Col4, 'MMMM', 'NewValue4')
                     WHEN col4 like '%NNNN%' THEN Replace(Col4, 'NNNN', 'NewValue5')
                   END )

edited Feb 2, 2016 at 6:14

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

answered Feb 2, 2016 at 6:06

yuvi

5745 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alkippe Nikephoros Over a year ago

Thank you, exactly what I wanted.

Gottfried Lesigang Over a year ago

@AlkippeNikephoros, If this is the solution for you - great! But keep in mind, that modifying XML data via string operations might bring up strange side effects. Just imagine an XML which has your search string on another place too, or an XML which contains "OOOO" and "LLLL". Don't know if this is possible in your case, but I would handle XML-data with XML methods...

Gottfried Lesigang · Accepted Answer · 2016-02-02 15:10:55Z

If you want to have a look on a solution using the XML abilities:

DECLARE @tbl TABLE(col1 INT,col2 VARCHAR(10),col3 VARCHAR(10),Col4 XML);
INSERT INTO @tbl VALUES
 (1,'A','E','<?xml version="1.0"?><vs><v> "OOOO"</v></vs>')  
,(2,'B','F','<?xml version="1.0"?><vs><v> "KKKK"</v></vs>')     
,(3,'C','G','<?xml version="1.0"?><vs><v> "LLLL"</v></vs>')  
,(4,'B','E','<?xml version="1.0"?><vs><v> "MMMM"</v></vs>')    
,(5,'T','G','<?xml version="1.0"?><vs><v> "NNNN"</v></vs>');

SELECT * FROM @tbl;

/*
1   A   E   <vs><v> "OOOO"</v></vs>
2   B   F   <vs><v> "KKKK"</v></vs>
3   C   G   <vs><v> "LLLL"</v></vs>
4   B   E   <vs><v> "MMMM"</v></vs>
5   T   G   <vs><v> "NNNN"</v></vs>
*/

Now you query and update this:

WITH MyData AS
(
    SELECT tbl.*
          ,tbl.Col4.value('(/vs/v)[1]','varchar(max)') AS ContentV
    FROM @tbl AS tbl
)
,ExtendedWithNewValue AS
(
    SELECT  *
           ,CASE  WHEN ContentV LIKE '%OOOO%' THEN 'NewValue1'
                  WHEN ContentV LIKE '%KKKK%' THEN 'NewValue2'
                  WHEN ContentV LIKE '%LLLL%' THEN 'NewValue3'
                  WHEN ContentV LIKE '%MMMM%' THEN 'NewValue4'
                  WHEN ContentV LIKE '%NNNN%' THEN 'NewValue5'
                  ELSE ContentV
            END AS NewValue
    FROM MyData
)
UPDATE @tbl
SET col4.modify('replace value of (/vs/v/text())[1] with sql:column("nv.NewValue")')
FROM @tbl AS tbl INNER JOIN ExtendedWithNewValue AS nv ON tbl.col1=nv.col1;

SELECT * FROM @tbl;

/*
col1    col2    col3    Col4
1   A   E   <vs><v>NewValue1</v></vs>
2   B   F   <vs><v>NewValue2</v></vs>
3   C   G   <vs><v>NewValue3</v></vs>
4   B   E   <vs><v>NewValue4</v></vs>
5   T   G   <vs><v>NewValue5</v></vs>
*/

Collectives™ on Stack Overflow

SQL Update Part of XML string in a column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related