How to update XML node value with new value in SQL server

Question

0.00/5 (No votes)

See more:

e.g.
DECLARE @Employee TABLE(empXML XML);
DECLARE @OldEmployeeId INT=103, @NewEmployeeId INT=999;

INSERT INTO @Employee VALUES('<root><Employee Id="100"/><Employee Id="102"/><Employee Id="103"/><Employee Id="104"/>')

SELECT * FROM @Employee

I want to update value of Employee Id to 999 where Employee Id =103.

What I have tried:

I tried this but no luck.

UPDATE @Employee SET
empXML.modify('replace value of (ROOT/Employee/@Id)[1] with sql:variable("@NewEmployeeId")')
WHERE empXML.value('(ROOT/Employee/@Id)[1]', 'INT') = @OldEmployeeId

Posted 25-Jan-17 0:09am

xpertzgurtej

Updated 21-Dec-19 23:06pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-01-25T05:50:00

Solution 1

Your XML is invalid - the <root> node is not closed.

XML is case-sensitive. In the inserted XML, the root element is called <root>, so an XPath query for ROOT isn't going to match it.

Your WHERE clause extracts the ID of the first <Employee> node, which is 100. That is not equal to your @OldEmployeeId variable, so there are no rows to update.

Your replace value of statement is replacing the ID of the first <Employee> node, which is not what you're trying to do.

Change your UPDATE query to:

SQL

UPDATE @Employee 
SET empXML.modify('replace value of (root/Employee[@Id = sql:variable("@OldEmployeeId")]/@Id)[1] with sql:variable("@NewEmployeeId")')
WHERE empXML.exist('root/Employee[@Id = sql:variable("@OldEmployeeId")]') = 1;

replace value of (XML DML)[^]
exist() Method (xml Data Type)[^]
XPath Reference[^]

Posted 25-Jan-17 5:50am

Richard Deeming

Comments

xpertzgurtej 26-Jan-17 23:36pm

thanks for your help..It worked same as i want..

Member 11996223 8-Dec-19 22:43pm

@Richard Deeming - can you please help me on below question from your solution?

empXML.modify('replace value of (root/Employee[@Id = sql:variable("@OldEmployeeId")]/@Id)

is there any possibilities to have a condition wild card checking. I mean, You are having condition like this [@Id = sql:variable("@OldEmployeeId")]. its checking exact value. I need, [@Id like %sql:variable("@OldEmployeeId")%].

How to handle this situation. Because, i having value with whitespace. otheriwse how to trim.

Thanks for the support.

Richard Deeming 9-Dec-19 5:56am

There doesn't seem to be a Like operator, but there is a contains function:
contains Function (XQuery) - SQL Server | Microsoft Docs[^]

WHERE empXML.exist('root/Employee[contains(@Id, sql:variable("@OldEmployeeId"))]') = 1

However, I'm not sure this would do what you want. If you were searching for 42, it would also return records with an ID of 1422, for example.

You might need to "shred" the XML and run the search in SQL instead.

Member 11996223 22-Dec-19 5:16am

@Richard deeming, Thanks. I will check. hope it will help me.

Member 11996223 22-Dec-19 8:59am

set @xmld.modify('replace value of (/Root/Identification[contains(Age,sql:variable("@age"))]/name/text())[1] with "123"')

while i am trying to run about code. i am getting error like,
"XQuery [modify()]: 'contains()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'"

- please guide me on this

Richard Deeming 7-Jan-20 14:01pm

That error would suggest that you're trying to modify multiple elements within the document; but that wouldn't fit with the [1] in your query.

Have you checked what the actual XML you're trying to update looks like?

Member 11996223 22-Dec-19 5:04am

@Richard Deeming, can you please help me below. In your above solution. For my case the condition checking column coming with spaces. how to trim this or how to check for that case.

Example:

UPDATE @Employee 
SET empXML.modify('replace value of (root/Employee[@Id = sql:variable("@OldEmployeeId")]/@Id)[1] with sql:variable("@NewEmployeeId")')
WHERE empXML.exist('root/Employee[@Id = sql:variable("@OldEmployeeId")]') = 1;

@Id - coming with leading space. so, my condition getting failed. Kindly guide me to resolve this.

Richard Deeming 7-Jan-20 14:06pm

There doesn't seem to be an option to trim the XML values in SQL, and there's no "like" operator. If contains doesn't work for you, you'll need to "shred" the XML and run the search in SQL.