72901

How to update a table based on an XML parameter

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.

<strong>How do I update the field with all the elements specified in the xml parameter?</strong>

Answer1:

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

Answer2:

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.

Answer3:

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.

Recommend

  • How to extract C source code from .so file?
  • Problem getting terminal output from ImageMagick's compare.exe ( Either by pipe or Python )
  • Angular appending is fast but destroying is slow(1 second)
  • Macro to cycle through and allocate data to members of structs incorrectly recognises struct member
  • Looping through Generic Class's collection property
  • Odoo POS create a dropdown list
  • File upload field is reset when submit form
  • How to Calculate Cumulative time difference in sql
  • Error 3251 on .oldValue control property
  • Lead() and LAG() functionality in SQL Server 2008
  • track changes of nodes bound in JavaFX
  • getting the range of a number column(min/max) if there are missing numbers in between
  • Changing Key Of An Property in Json
  • enumerate days of week in t-sql
  • How do I create closures for model getter-setter in angular?
  • How to prevent TreeItem selection?
  • Query timeout expired in django-mssql when executing custom SQL directly
  • mapping between two ontologies
  • Pre-populated SQLite Database not reading properly in Android Studio
  • Prevent page break in text block with iText, XMLWorker
  • Converting query results into DataFrame in python
  • Need code translation from VB to C#
  • Python 3.2.2, error(scripts to exe)
  • Best practice to eliminate magic numbers within a member function
  • NSIS decompiler
  • Possible to get mouse events fired when cursor is outside page?
  • Eliminate partial duplicate rows from result set
  • Problem while Building a Setup Project for a windows Service?
  • Mysterious problem with floating point in LISP - time axis generation
  • How to know when stdin is empty if it contains EOF?
  • Controls, properties, events and timers running in design time
  • Updated Ionic CLI but shows previous version (Windows)
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • php design question - will a Helper help here?
  • AngularJs get employee from factory
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Reading document lines to the user (python)