I have an XML variable that looks like this:
<code>
<IDs>
<ID id="1">a</ID>
<ID id="43">d</ID>
<ID id="3">b</ID>
</IDs>
</code>
I want to use that in a stored procedure (SQL Server) that will update a table.
My table look like this:
ID INT,
a INT,
b INT,
c INT,
d INT
The statement should increase the letter value associated with the id.
SO it would look like this:
Table Row with ID = 1, update column "a" by increasing the current value by 1.
Table Row with ID = 43 - update column "d" by increasing current value by 1.
Finally Table row with ID= 3 - update column "b" by increasing value by 1.
This is what I have so far - (The second line is where i need the most help.):
Update MyTable
SET @letter = letterVal +1
WHERE ID IN(
SELECT x.v.value('@id','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
)
You would have to do something along the lines of this:
DECLARE @input XML = '<code>
<IDs>
<ID id="1">a</ID>
<ID id="43">d</ID>
<ID id="3">b</ID>
</IDs>
</code>'
;WITH ParsedXML AS
(
SELECT
ID = C.value('(@id)[1]', 'int'),
ColumnName = C.value('(.)[1]', 'varchar(10)')
FROM @Input.nodes('/code/IDs/ID') AS T(C)
)
UPDATE MyTable
SET a = CASE WHEN p.ColumnName = 'a' THEN t.a + 1 ELSE t.a END,
b = CASE WHEN p.ColumnName = 'b' THEN t.b + 1 ELSE t.b END,
c = CASE WHEN p.ColumnName = 'c' THEN t.c + 1 ELSE t.c END,
d = CASE WHEN p.ColumnName = 'd' THEN t.d + 1 ELSE t.d END
FROM MyTable t
INNER JOIN ParsedXml p ON t.ID = p.ID
SELECT * FROM Mytable
This will do it - but it's really quite ugly. The main problem is: you cannot get the column name as a value from somewhere else, to use it in the UPDATE
statement - unless you go the dynamic SQL route, which has its own set of pros and cons and can get rather messy.
If you're interested in dynamic SQL - The Curse and Blessings of Dynamic SQL by Erland Sommarskog is an absolute must-read - read it before you launch into using dynamic SQL!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With