Find and replace just a part of a xml value using XQuery?

Question

I have an XML in one of my columns, that is looking something like this:

<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path3/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path21/anothertest/second.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path15/test123/file.doc</VorlagenHistorie>
</BenutzerEinstellungen>

I would like to replace all test123 occurances (there can be more than one) in VorlagenHistorie with another test, that all paths direct to test123 after my update.

I know, how you can check and replace all values with an equality-operator, I saw it in this answer: Dynamically replacing the value of a node in XML DML

But is there a CONTAINS Operator and is it possible to replace INSIDE of a value, I mean only replace a part of the value?

Thanks in advance!

Is there always one `State` element and a list of `VorlagenHistorie` elements and nothing else? — Shnugo, Aug 25 '16 at 08:53
The State object is only an example, there a ton other elements in the list. — Jannik, Aug 25 '16 at 08:59

Shnugo · Accepted Answer · 2016-08-25T09:42:50.357

I would not suggest a string based approach normally. But in this case it might be easiest to do something like this

declare @xml XML=
'<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>';

SELECT CAST(REPLACE(CAST(@xml AS nvarchar(MAX)),'/test123/','/anothertest/') AS xml);

UPDATE

If this approach is to global you might try something like this:

I read the XML as derived table and write it back as XML. In this case you can be sure, that only Nodes with VorlageHistorie will be touched...

SELECT @xml.value('(/BenutzerEinstellungen/State)[1]','nvarchar(max)') AS [State]
      ,(
        SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
        FROM @xml.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
        FOR XML PATH('VorlagenHistorie'),TYPE
       )
FOR XML PATH('BenutzerEinstellungen');

UPDATE 2

Try this. It will read all nodes, which are not called VorlagenHistorie as is and will then add the VorlageHistorie nodes with replaced values. The only draw back might be, that the order of your file will be different, if there are other nodes after the VorlagenHistorie elements. But this should not really touch the validity of your XML...

declare @xml XML=
'<BenutzerEinstellungen>
       <State>Original</State>
       <Unknown>Original</Unknown>
       <UnknownComplex>
       <A>Test</A>
       </UnknownComplex>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>';

SELECT @xml.query('/BenutzerEinstellungen/*[local-name(.)!="VorlagenHistorie"]') AS [node()]
      ,(
        SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
        FROM @xml.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
        FOR XML PATH('VorlagenHistorie'),TYPE
       )
FOR XML PATH('BenutzerEinstellungen');

UPDATE 3

Use an updateable CTE to first get the values and then set them in one single go:

declare @tbl TABLE(ID INT IDENTITY,xmlColumn XML);
INSERT INTO @tbl VALUES
(
'<BenutzerEinstellungen>
       <State>Original</State>
       <Unknown>Original</Unknown>
       <UnknownComplex>
       <A>Test</A>
       </UnknownComplex>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>')
,('<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
    </BenutzerEinstellungen>');

WITH NewData AS
(
    SELECT ID
      ,xmlColumn AS OldData
      ,(
        SELECT t.xmlColumn.query('/BenutzerEinstellungen/*[local-name(.)!="VorlagenHistorie"]') AS [node()]
              ,(
                SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
                FROM t.xmlColumn.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
                FOR XML PATH('VorlagenHistorie'),TYPE
               )
        FOR XML PATH('BenutzerEinstellungen'),TYPE
       ) AS NewXML
    FROM @tbl AS t
)
UPDATE NewData
SET OldData=NewXml;

SELECT * FROM @tbl;

We have other elements, that could have the same string inside, but shouldnt be updated. Its very unlikely, but would be a risk. — Jannik, Aug 25 '16 at 09:00
@Jannik see my update... If there are more attributes you can just include them like I've done it with `State` — Shnugo, Aug 25 '16 at 09:01
Well, we don't know them all, and they are pretty much dynamic unfortunately. — Jannik, Aug 25 '16 at 09:02
Thank you, this looks promising. Can you tell me, how you execute that on a real table? — Jannik, Aug 25 '16 at 09:34
I have made a small mistake. Would it work with something like this? Original Original Test /path/path/test123/file.doc /path/path/test123/file123.doc /path/path/anothertest/second.doc — Jannik, Aug 29 '16 at 10:19
There is a problem with the pasted XML... the second and the third closing `` includes weird characters between the `n` and the `t`. There is 8204 and 8203 (unicode). However this got into your tag names, [this might be related[(http://stackoverflow.com/q/2973698/5089204). If you need further help: Please give details about *Would it work with something like this*. And please open a new question and place a link to this to avoid chameleon questions, Thx. — Shnugo, Aug 29 '16 at 10:50
Btw: I just tested this with your XML from comment and wrote the first `innerElement` via copy'n'paste into the bad closing tags. Seems to work though... — Shnugo, Aug 29 '16 at 10:54
It was just an example pseudo XML. Do you have a solution for that one aswell? I would need to replace the innerElement values and not the VorlagenHistorie-values. It seems like thats not as simple as changing a path in your Update3. Maybe you have an idea how to update your script. Your script worked perfectly in the other XML-structure. It would be the last thing, I asked regarding this Topic. — Jannik, Aug 29 '16 at 11:18
@Jannik, no problem, you can ask as much as you like, but please start a new question. This one gets overloaded. SO is - beside your hope to find a solution for your issue - a Q&A-collection for future visitors. Such questions will not help other people... So the principlce is KISS (Keep it small and simple, one issue, one question, one solution). So: Please start a new question with a link to this (add a comment with the link and `@Shnugo`. Paste example XML (reduced, but with all varieties) and will pop up there soon. — Shnugo, Aug 29 '16 at 11:28
@Jannik, one question to help you to articulate the new question: Is this new structure with `innerElement` now the new *one-and-only* structure or do you have both (the former without `innerElement` and this? If there are `innerElements` are there other values *within* `VorlageHistorie` which should not be changed? — Shnugo, Aug 29 '16 at 11:30

score 2 · Answer 2 · answered Aug 25 '16 at 09:09

2

A weird solution, but it worked well:

DECLARE @xml XML = '
<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path5/test123/third.doc</VorlagenHistorie>
</BenutzerEinstellungen>';

DECLARE @Counter int = 1,
        @newValue nvarchar(max),
        @old nvarchar(max) = N'test123',
        @new nvarchar(max) = N'anothertest';

WHILE @Counter <= @xml.value('fn:count(//*//*)','int')
BEGIN
    SET @newValue = REPLACE(CONVERT(nvarchar(100), @xml.query('((/*/*)[position()=sql:variable("@Counter")]/text())[1]')), @old, @new)
    SET @xml.modify('replace value of ((/*/*)[position()=sql:variable("@Counter")]/text())[1] with sql:variable("@newValue")');
    SET @Counter = @Counter + 1;
END

SELECT  @xml;

Output:

<BenutzerEinstellungen>
  <State>Original</State>
  <VorlagenHistorie>/path/path/anothertest/file.doc</VorlagenHistorie>
  <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
  <VorlagenHistorie>/path/path5/anothertest/third.doc</VorlagenHistorie>
</BenutzerEinstellungen>

answered Aug 25 '16 at 09:09

gofr1

15,741
11
42
52

Funny, this is the approach I just suggest below Pawel's answer :-) – Shnugo Aug 25 '16 at 09:13
HI gofr1, I used your answer to place one more answer, because I think your approach can be widely enhanced by using more specific XPath. I vote this up, as I've *stolen* your code, but you've *stolen* Pawel's code, so I hope this is OK for you :-) – Shnugo Aug 25 '16 at 09:19
Hi @Shnugo it is OK for sure :) Honestly, I didn't read recent comments beyond Pawel's answer, I just post this solution as is. I don't think it is good way for current OP situation, but it can help another fellow developers. – gofr1 Aug 25 '16 at 09:32
Hi gofr1, you did not missing anything! Your answer came in in the same second as I posted my comment. We just had the same idea... – Shnugo Aug 25 '16 at 09:44
@Shnugo it just happens, I guess :) – gofr1 Aug 25 '16 at 09:47

score 1 · Answer 3 · answered Aug 25 '16 at 07:48

1

If @shnugo's answer does not fit your needs, you can use XML/XQuery approach:

DECLARE @xml xml = '<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
    </BenutzerEinstellungen>';
DECLARE @from nvarchar(20) = N'test123';
DECLARE @to nvarchar(20) = N'another test';
DECLARE @newValue nvarchar(100) = REPLACE(CONVERT(nvarchar(100), @xml.query('(/BenutzerEinstellungen/VorlagenHistorie/text()[contains(.,sql:variable("@from"))])[1]')), @from, @to)

SET @xml.modify('
    replace value of (/BenutzerEinstellungen/VorlagenHistorie/text()[contains(.,sql:variable("@from"))])[1]
    with sql:variable("@newValue")')

SELECT @xml

answered Aug 25 '16 at 07:48

Paweł Dyl

8,888
1
11
27

I will try this Approach and give you some Feedback :) – Jannik Aug 25 '16 at 08:36
And what if there is one more node like `/path/path/test123/third.doc`? Or `VorlagenHistorie` nodes content will switch there places? – gofr1 Aug 25 '16 at 08:38
There can be more than one. This won't work with more than one, right? – Jannik Aug 25 '16 at 08:42
Pawel, you are using the first `VorlagenHistorie` to build up your `@newValue`. But there are more than one. You migh count the number of `VorlagenHistorie` nodes and use a loop to inject the node's index as `sql:variable`... – Shnugo Aug 25 '16 at 09:11

score 0 · Answer 4 · answered Aug 25 '16 at 09:17

gofr1's answer might be enhanced by using more specific XPath expressions:

DECLARE @Counter int = 1,
        @newValue nvarchar(max),
        @old nvarchar(max) = N'test123',
        @new nvarchar(max) = N'anothertest';

WHILE @Counter <= @xml.value('fn:count(/BenutzerEinstellungen/VorlagenHistorie)','int')
BEGIN
    SET @newValue = REPLACE(CONVERT(nvarchar(100), @xml.value('(/BenutzerEinstellungen/VorlagenHistorie)[sql:variable("@Counter")][1]','nvarchar(max)')), @old, @new)
    SET @xml.modify('replace value of (/BenutzerEinstellungen/VorlagenHistorie[sql:variable("@Counter")]/text())[1] with sql:variable("@newValue")');
    SET @Counter = @Counter + 1;
END

SELECT  @xml;

Find and replace just a part of a xml value using XQuery?

4 Answers4

UPDATE

UPDATE 2

UPDATE 3