SQL Server XQuery - Modify the text of a closed element

Question

I have an XML column in SQL Server 2005 with the following simplified XML.

+----------+------------------+
| RecordID | ValueXML         |
+----------+------------------+
| 1        | <value>x</value> |
|          |                  |
+----------+------------------+

If I want to change that value to y, I use the following SQL statement.

update ValueTable
set ValueXML.modify('
    replace value of (/value/text())[1]
    with ("y")')
where RecordID = 1

That works fine. x becomes y. The problem happens if value is set to nothing.

update ValueTable
set ValueXML.modify('
    replace value of (/value/text())[1]
    with ("")')
where RecordID = 1

Once this happens, the table looks like this.

+----------+------------------+
| RecordID | ValueXML         |
+----------+------------------+
| 1        | <value />        |
|          |                  |
+----------+------------------+

Now, I can no longer set the value. The replace value query executes successfully, but makes no change.

How can I set the text for a closed XML element? Is it possible to "unclose" the element?

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

You can't insert a value like that because you are trying to replace the text but there is no text to replace, so it will execute but it will not work.

This seems to be the same problem you're having here.

edited May 23 '17 at 12:23

Community

1
1

answered May 16 '16 at 13:29

BoogaWooga

108
7

score 2 · Accepted Answer · answered May 16 '16 at 13:48

2

Thanks @BoogaWooga for pointing me in the right direction. I was able to do my replace in two statements.

First, I ensured the element was closed. This statement works whether the element is closed or not.

update ValueTable
set ValueXML.modify('
    replace value of (/value/text())[1]
    with ("")')
where RecordID = 1

Now that I know the element is closed, I use insert text to insert a value into the closed element.

update ValueTable
set ValueXML.modify('
    insert text{"x"}
    into (/value[1])')
where RecordID = 1

answered May 16 '16 at 13:48

dangowans

2,263
3
25
40

@Shnugo I would have voted it up, however, the content of the answer did not answer my question. The content of the answer says "you can't insert a value like that". That's true, but you can insert the value. You just need to do it differently. I was looking for what that "differently" was. – dangowans May 17 '16 at 17:36
@Shnugo Maybe if my answer, which in my opinion is more correct, has more votes, or once I'm able to mark my answer as correct, I will vote up the answers that helped me, but until then, I'd prefer not to push those answers higher than my own. – dangowans May 17 '16 at 17:45
dangowans, I have a different understanding obviously... I vote on **very many** contributions. Everything I find useful, everything which adds something interesting is worth an upvote for me. Another thing is the acceptance. The answer, which really solves the issue, gets it. If there are useful answers but no solution, I'd write my own answer and accept this. But all useful contributions get the upvote. That is the fuel SO is running with... – Shnugo May 17 '16 at 20:28

SQL Server XQuery - Modify the text of a closed element

2 Answers2