CODEWITHSUNDEEP
oracle12cxmltype
milheiros
milheiros

Reputation: 619

XMLTYPE update replacing values in last tag in Oracle 12c

I have a XMLTYPE with 3 tag elements of the same name. I want to replace ';' with a '.' in the last one. Something like this:

SELECT XMLQUERY (
          'copy $tmp := . modify
            (for $i in $tmp/root/test/text()  
             return replace value of node $i
                    with ''.'') 
            return $tmp'
          PASSING XMLTYPE (
                     '<root><test>asdasdsada;</test><test>lkjasdhalskdjhlakjsdh;</test><test>tirari;</test></root>')
          RETURNING CONTENT)
  FROM DUAL

My result shoud be like this;

<root><test>asdasdsada;</test><test>lkjasdhalskdjhlakjsdh;</test><test>tirari.</test></root>

Thanks

Upvotes: 0

Views: 134

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

At the moment you're modifying all of the test nodes, and you are replacing the contents completely with a period. From your expected output that isn't what you want.

You can count the number of test nodes into a variable, and then use [postition()=$n] to only apply your replace to the final node. You can then use the replace() or translate() function to only change the ; to ., rather than replacing the entire text content of the node:

SELECT XMLQUERY (
          'copy $tmp := . modify
            (let $n := count($tmp/root/test)
            for $i in $tmp/root/test[position()=$n]/text()  
             return replace value of node $i with replace($i, ";", "."))
            return $tmp'
          PASSING XMLTYPE (
                     '<root><test>asdasdsada;</test><test>lkjasdhalskdjhlakjsdh;</test><test>tirari;</test></root>')
          RETURNING CONTENT) AS RESULT
  FROM DUAL;

RESULT                                                                                                                  
------------------------------------------------------------------------------------------------------------------------
<root><test>asdasdsada;</test><test>lkjasdhalskdjhlakjsdh;</test><test>tirari.</test></root>

Upvotes: 1

Related Questions