Replace/insert XML text node in XQuery

Question

Consider the following code to update "/project/cost/final" in an untyped XML.

The code will run successfully, however, the value actually remains unchanged.

DECLARE @xml XML;

SET @xml = '
<project>
  <cost>
    <budget>100</budget>
    <estimated>92</estimated>
    <final />
  </cost>
</project>
';

SET @xml.modify('
  replace value of (/project[1]/cost[1]/final[1]/text()[1]) with 95
');

SELECT @xml;

I understand that the "<value name="final" />" element has no text node at the moment, so there is nothing to replace.

So I should insert text node instead, and it will work:

SET @xml.modify('
  insert text{"95"} into (/project[1]/cost[1]/final[1])
');

But I need to first check if the text node exists, and it brings a lot of complication to my program.

How can I update the value without checking/inserting "text"?

Note: I am working with untyped XML.

SQL Server is not optimised for XML shredding or the CRUD operations on XML data type. The performance will be absolutely horrendous. Any XML manipulation is best done in the application layer. Use SQL Server just for the storage. — M.Ali
– M.Ali, Commented Sep 5 at 11:14
As a newbie, I just got to know the XQuery implementation is quite clumsy and incomplete. Manipulating XML in the application layer is also an option. But I find doing it in SQL Server still more perfomant in my specific case. I guess it is because it avoids reading/writing the full document and network round-trip. Maybe it depends on the volume and structure of data. — Miles Fan
– Miles Fan, Commented Sep 5 at 21:34

Yitzhak Khabinsky · Accepted Answer · 2025-09-04 17:02:44Z

1

It is a limitation of SQL Server's XQuery implementation.

Please try the following solution that is covering both scenarios, i.e. when the final XML element has textual value or not.

Notable points:

We can use T-SQL variable instead of hard-coded values in XQuery.
No need to mushroom the [1] predicate in the XPath expressions.

SQL

DECLARE @xml XML = 
N'<project>
  <cost>
    <budget>100</budget>
    <estimated>92</estimated>
    <final />
  </cost>
</project>';

DECLARE @final INT = 770;

-- remove any text before inserting
SET @xml.modify('
  delete (/project/cost/final/text())[1]
');

-- Insert text into the <final> node
SET @xml.modify('insert text {sql:variable("@final")} as first into (/project/cost/final)[1]');

-- test
SELECT @xml;

Output

<project>
  <cost>
    <budget>100</budget>
    <estimated>92</estimated>
    <final>770</final>
  </cost>
</project>

edited Sep 4 at 17:02

answered Sep 4 at 12:45

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

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

3 Comments

Miles Fan Sep 5 at 21:22

Thanks, it works. BTW, is there any way to use variables in the XPath, like "DECLARE @cost_type varchar(20) = 'final'; SET @xml.modify('delete (/project/cost/{sql:variable("@cost_type")}/text())[1]');" ? It does not work now, but I am wondering how to do it.

Yitzhak Khabinsky Sep 7 at 0:47

Unfortunately, XPath expression should be literal, no expressions are allowed. You would need to make the entire SQL statement dynamic to have its XPath expression dynamic.

Miles Fan Sep 7 at 7:22

Thanks for the explanation. I have to be cautious about SQL injection attacks in such dynamic SQL statement. Sanitizing user input seems inevitable in my case.

Michał Turczyn · Accepted Answer · 2025-09-06 05:03:15Z

1

You could remove the the node before inserting it to ensure it will be empty.

DECLARE @xml XML;

SET @xml = '
<project>
  <cost>
    <budget>100</budget>
    <estimated>92</estimated>
    <final>100</final>
  </cost>
</project>
';

-- remove text before inserting
SET @xml.modify('
  delete /project[1]/cost[1]/final[1]/text()
');

SET @xml.modify('
  insert text{"95"} into (/project[1]/cost[1]/final[1])
');

SELECT @xml;

edited Sep 6 at 5:03

answered Sep 4 at 8:58

Michał Turczyn

41.3k18 gold badges58 silver badges88 bronze badges

4 Comments

siggemannen Sep 4 at 14:58

What if he wants to keep old value if there is any

Miles Fan Sep 5 at 21:14

I think we need to insert after deletion, instead of replace after deletion. There is no text node to replace after deletion. Or can we blame XML specs being too rigid?

Michał Turczyn Sep 6 at 5:03

Of course i meant insert :) i did not even realize what i wrote until now :O

Miles Fan Sep 7 at 7:38

But speaking of this, I do expect insertation should automatically happen for an element, which has no children, and no text node to replace. This saves the world a lot of efforts. I think there must be adquate reasons behind that prove my idea actually naive, but I wonder what would those be.

Collectives™ on Stack Overflow

Replace/insert XML text node in XQuery

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related