How to update a table based on an XML parameter

Question

I have a table and I want to update one of its varchar fields based on the values in an XML parameter.

I have the following table:

ID  Constraint_Value
1   (OldVal_1) (OldVal_2)
2   (OldVal_2) (OldVal_1)

and I want to use the following XML to update the Constraint_Value field:

<qaUpdates>
    <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate>
    <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate>
</qaUpdates>

After the update, I am aiming for the following:

ID    Constraint_Value
1     (NewVal_1) (NewVal_2)
2     (NewVal_2) (NewVal_1)

The following SQL illustrates my problem (which you can run in SQL Management Studio without any set up) :

IF OBJECT_ID('tempdb..#tmpConstraint') IS NOT NULL DROP TABLE #tmpConstraint
GO 

CREATE TABLE tempdb..#tmpConstraint ( constraint_id INT PRIMARY KEY, constraint_value varchar(256) )
GO

insert into #tmpConstraint
values (1, '(OldVal_1) (OldVal_2)')

insert into #tmpConstraint
values (2, '(OldVal_2) (OldVal_1)')

select * from #tmpConstraint

declare @myXML XML
set @myXML = N'<qaUpdates>
    <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate>
    <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate>
</qaUpdates>'

update c
set constraint_value = REPLACE(constraint_value, Child.value('(old)[1]', 'varchar(50)'), Child.value('(new)[1]', 'varchar(50)'))
from #tmpConstraint c
cross join @myXML.nodes('/qaUpdates/qaUpdate') as N(Child) 

select * from #tmpConstraint

This gives the results:

(Before)
1   (OldVal_1) (OldVal_2)
2   (OldVal_2) (OldVal_1)

(After)
1   (NewVal_1) (OldVal_2)
2   (OldVal_2) (NewVal_1)

As you can see just OldVal_1 has been updated. OldVal_2 has remained the same.

How do I update the field with all the elements specified in the xml parameter?

Chris Moutray · Accepted Answer · 2012-06-01 05:49:51Z

2

Making use of a recursive cte allows me to get the result you're looking for. As the following shows. But att least its not a cursor/while-loop ;)

declare @tmpConstraint table (ID int , Constraint_Value varchar(256))
insert into @tmpConstraint values 
(1, '(OldVal_1) (OldVal_2)'),
(2, '(OldVal_2) (OldVal_1)')

declare @myXML XML
set @myXML = N'<qaUpdates>
    <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate>
    <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate>
</qaUpdates>'

declare @xmlData table (oldValue varchar(256), newValue varchar(256))
insert into @xmlData 
select 
    oldValue = Child.value('(old)[1]', 'varchar(50)'), 
    newValue = Child.value('(new)[1]', 'varchar(50)')
from @myXML.nodes('/qaUpdates/qaUpdate') as N(Child)

The above was just setup for the following.

;with cte (ID, Constraint_Value, CLevel)
as
(
    select c.ID, c.Constraint_Value, 1
    from @tmpConstraint c

    union all

    select p.ID, cast(replace(p.Constraint_Value, x.oldValue, x.newValue) as varchar(256)), p.CLevel + 1
    from cte p
    join @xmlData x on p.Constraint_Value like '%' + x.oldValue + '%'
)
update c
set c.Constraint_Value = t.Constraint_Value
from @tmpConstraint c
join (
    select 
        *,
        rn = row_number() over (partition by ID order by CLevel desc)
    from cte
) t on t.ID = c.ID and rn = 1

select * from @tmpConstraint

edited Jun 1, 2012 at 5:49

answered May 31, 2012 at 19:22

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

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

2 Comments

Mark Robinson Over a year ago

Great answer, @mouters. Using cte's is a very clever solution. Thanks.

GilM Over a year ago

This is a nice solution, but it's really a loop hidden in the CTE. Also be aware that the CTE can have many more rows in it than the original table (for each row with n matching values, you'll get 1 + n + n(n-1) + n(n-1)(n-2)+...n! rows). Also be aware that the default OPTION (MAXRECURSION n) value is 100. But, for moderately sized tables with few matches per row, I like it.

GilM · Accepted Answer · 2012-05-31 19:23:53Z

1

I think the issue here has nothing to do with XML. It's that a single UPDATE will only update each row once, regardless of how many joined rows exist. I think you could add a WHERE clause and a WHILE loop to get all of your substitutions:

WHILE @@ROWCOUNT>0
BEGIN
  update c 
  set constraint_value = REPLACE(constraint_value, Child.value('(old)[1]', 'varchar(50)'), Child.value('(new)[1]', 'varchar(50)')) 
  from #tmpConstraint c 
  cross join @myXML.nodes('/qaUpdates/qaUpdate') as N(Child)  
  WHERE constraint_value LIKE '%' + Child.value('(old)[1]', 'varchar(50)') + '%'
END

Just be sure that this follows a statement that sets @@RowCount.

answered May 31, 2012 at 19:23

GilM

3,78120 silver badges18 bronze badges

Comments

KickerCost.com · Accepted Answer · 2012-06-01 18:09:34Z

I realize this has already been answered but I was curious to see if there was a way to do this without using a cte. At any rate the bigger problem is really that you are storing 2 pieces of data in the same column/row. This coupled with the fact that you cannot update the same row twice within a single update statement is causing your issues. Anyhow my approach was this( I apologize ahead of time for complexity):

DECLARE @tmpConstraint TABLE (
    constraint_id INT PRIMARY KEY
    ,constraint_value VARCHAR(256)
    )

INSERT INTO @tmpConstraint
VALUES (
    1
    ,'(OldVal_1) (OldVal_2)'
    )

INSERT INTO @tmpConstraint
VALUES (
    2
    ,'(OldVal_2) (OldVal_1)'
    )

INSERT INTO @tmpConstraint
VALUES (
    3
    ,'(OldVal_3) (OldVal_21) (OldVal_1)'
    )

DECLARE @myXML XML

SET @myXML = N'<qaUpdates>     <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate>     <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate> </qaUpdates>'

SELECT *
FROM @tmpConstraint

UPDATE C
SET constraint_value = c.New_Val
FROM (
    SELECT Constraint_ID UpdID
        ,Constraint_value
        ,STUFF((
                SELECT (' ' + New_value)
                FROM (
                    --Converts XML into a Table effectively splitting the string
                    SELECT constraint_id
                        ,t.value('.', 'varchar(200)') Current_value
                        ,Coalesce(Nullif('(' + new + ')', '()'), t.value('.', 'varchar(200)')) New_Value
                    FROM
                        --Converts single column into an xml document to split rows. Uses a blank space as the identifer of rows
                        (
                        SELECT constraint_id
                            ,convert(XML, ('<R>' + replace(constraint_value, ' ', '</R><R>')) + '</R>') xmldoc
                        FROM @tmpConstraint
                        ) AS a
                    CROSS APPLY a.xmldoc.nodes('./R') AS b(t)
                    --Join to table containing proposed changes based on value to change            
                    LEFT JOIN (
                        SELECT Child.value('./old[1]', 'varchar(100)') old
                            ,Child.value('./new[1]', 'varchar(100)') new
                        FROM @myXML.nodes('/qaUpdates/qaUpdate') AS N(Child
                        )
                    ) q2 ON '(' + old + ')' = t.value('.', 'varchar(200)')
                ) Modified WHERE Modified.constraint_id = base.constraint_id FOR XML path(''))
        ,1,1,'') New_Val
FROM @tmpConstraint Base ) c

SELECT *
FROM @tmpConstraint

It looks alot messier than it is and could be cleaned up if you had some UDF's in there. But basicaly I break your multivalue column into multiple rows. Turning this

1   (OldVal_1) (OldVal_2)
2   (OldVal_2) (OldVal_1)
3   (OldVal_3) (OldVal_21) (OldVal_1)

into this

1   (OldVal_1)
1   (OldVal_2)
2   (OldVal_2)
2   (OldVal_1)
3   (OldVal_3)
3   (OldVal_21)
3   (OldVal_1)

I do the same with the xml file. Turning it into a Key data pair set like this

OldVal_1    NewVal_1
OldVal_2    NewVal_2

Join that with the table created before to get this (duplicating the orignal value where there was no replacement determined)

1   (OldVal_1)  (NewVal_1)
1   (OldVal_2)  (NewVal_2)
2   (OldVal_2)  (NewVal_2)
2   (OldVal_1)  (NewVal_1)
3   (OldVal_3)  (OldVal_3)
3   (OldVal_21) (OldVal_21)
3   (OldVal_1)  (NewVal_1)

Recombine the seperated rows into a single string again grouped by constraint id like so:

1   (OldVal_1) (OldVal_2)       (NewVal_1) (NewVal_2)
2   (OldVal_2) (OldVal_1)       (NewVal_2) (NewVal_1)
3   (OldVal_3) (OldVal_21) (OldVal_1)   (OldVal_3) (OldVal_21) (NewVal_1)

Then use that in my from statement to update the data in the orignal table. Anyhow I realize the code could be cleaned up a bit and probably even simplified but thats the concept I came up with. Really the bigger problem is how you are storing this data. But your circumstances are unknown to me so Ill reserve judgement.

Collectives™ on Stack Overflow

How to update a table based on an XML parameter

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related