SQL & PL/SQL

Hi,

My knowledge of XQuery being limited, I'm trying to find the proprer syntax to “merge” existing nodes and delete some unmatched ones.

Suppose I have the following xml data input:

<root>
   <tag1>1</tag1>
   <tag2>2</tag2>
   <val_list>
     <val>
       <id>1</id>
       <value>123</value>
       <comments>blabla1</comments>
     </val>
     <val>
       <id>2</id>
       <value>456</value>
       <comments>blabla2</comments>
    </val>
    <val>
       <id>3</id>
       <value>789</value>
       <comments>blabla3</comments>
    </val>
  </val_list>
</root>

In this data I'm interested in merging the <val_list> node that consists of several “val” blocks with another <val_list_computed>.

<val_list_computed> looks like this:

   <val_list_computed>
     <val>
       <id>1</id>
       <value>123</value>
     </val>
     <val>
       <id>3</id>
       <value>789</value>
    </val>
    <val>
       <id>4</id>
       <value>0</value>
    </val>
   </val_list_computed>

So for each <val> of the input data, if its <id> is present in a <val> of <val_list_computed>, it should be kept and untouched. If <id> is not present in a <val> of <val_list_computed>, then delete the whole <val> node. If there's a <val> in <val_list_computed> whose <id> is not found in a <val> of <val_list>, then add it.

So here, the desired output should be:

<root>
   <tag1>1</tag1>
   <tag2>2</tag2>
   <val_list>
     <val>
       <id>1</id>
       <value>123</value>
       <comments>blabla1</comments>
     </val>    
    <val>
       <id>3</id>
       <value>789</value>
       <comments>blabla3</comments>
    </val>
     <val>
       <id>4</id>
       <value>0</value>
    </val>
  </val_list>
</root>

id 1 and 3 are kept, id 2 is removed, and id 4 is added. Of course this is just sample data, there'll be unknown blocs of <val>.

Here's the query to generate the sample output.

with xml_data as 
(select xmltype
('<root>
   <tag1>1</tag1>
   <tag2>2</tag2>
   <val_list>
     <val>
       <id>1</id>
       <value>123</value>
       <comments>blabla1</comments>
     </val>
     <val>
       <id>2</id>
       <value>456</value>
       <comments>blabla2</comments>
    </val>
    <val>
       <id>3</id>
       <value>789</value>
       <comments>blabla3</comments>
    </val>
  </val_list>
</root>'
) x
from dual)
select * from xml_data;   

Thanks