CODEWITHSUNDEEP
xmloracle-databasexquery
Rachcha
Rachcha

Reputation: 8816

Replace value of an empty node with 0

The following is my sample XML:

<root>
    <item>old_value</item>
    <item/>
<root>

I have a table with XML data in an XMLTYPE column and I have so far managed to query the rows which have at least one empty item node, like the one shown above.

Now, I want to replace the empty node with <item>0</item> so that my XML looks like:

<root>
    <item>old_value</item>
    <item>0</item>
<root>

I tried to look here and here on Oracle Forums but found no answer that I could use.

Upvotes: 0

Views: 1346

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21115

The example in the link 1 in the question works only for one element item in the document. If there are more than one item elements, as in the exemple; a slightly modified XMLQuery should be used:

 select xmlquery(
            'copy $d := .
             modify (
          for $i in $d/root/item 
          where string-length($i) = 0 
          return replace value of node $i with $val            
             )
            return $d'
            passing xmlparse(document '<root><item>old_value</item><item/><item/></root>')
                  ,'new_value' as "val"
            returning content
         ) as result
 from dual;  


 <root><item>old_value</item><item>new_value</item><item>new_value</item></root>

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

create table test_xml(id number, doc xmltype);
insert into test_xml values(1,xmltype('<root><item>old_value</item><item/><item1/><item2/></root>'));
insert into test_xml values(2,xmltype('<root><item>old_value</item><item/><item1/><item2/></root>'));
UPDATE test_xml SET doc =
   UPDATEXML(doc,
   '/root/item[not(text())]',xmltype('<item>0</item>')) where id =1;
---
UPDATE test_xml SET doc =
   appendChildXML(doc,
   '/root/*[not(text())]',xmltype('<any>0</any>').extract('/any/text()'))

First update replace empty item with zero-item
Second update append text-node =0 to all empty node.

Upvotes: 1

Related Questions