The logic to follow is:
- Extract < XMLDoc > nodes that have the same destCode
- Aggregate the nodes into a single one
- Add the parent node < Documents >
Consider the following sample data:
The query you need:HTML Code:SQL> create table xml_temp 2 (SrcCode Char(5), 3 DestCode Char(5), 4 XMLDoc XMLType); Table created. SQL> insert into xml_temp values 2 ('SC1', 'DC1', '<XMLDoc> 3 <Node1>1-DC1-A</Node1> 4 <Node2>1-DC1-B</Node2> 5 </XMLDoc>'); 1 row created. SQL> insert into xml_temp values 2 ('SC1', 'DC2', '<XMLDoc> 3 <Node1>1-DC2-A</Node1> 4 <Node2>1-DC2-B</Node2> 5 </XMLDoc>'); 1 row created. SQL> insert into xml_temp values 2 ('SC1', 'DC1', '<XMLDoc> 3 <Node1>1-DC1-C</Node1> 4 <Node2>1-DC1-D</Node2> 5 </XMLDoc>'); 1 row created. SQL> select x.XMLDoc.EXTRACT('/*') xml from xml_temp x; XML ---------------------------------------------------- <XMLDoc> <Node1>1-DC1-A</Node1> <Node2>1-DC1-B</Node2> </XMLDoc> <XMLDoc> <Node1>1-DC2-A</Node1> <Node2>1-DC2-B</Node2> </XMLDoc> <XMLDoc> <Node1>1-DC1-C</Node1> <Node2>1-DC1-D</Node2> </XMLDoc>HTML Code:SQL> SELECT XMLELEMENT("Documents", 2 XMLAGG( 3 EXTRACT(x.XMLDoc, '/XMLDoc') 4 ) 5 ).EXTRACT('/*') xml 6 FROM xml_temp x 7 WHERE x.DestCode = '&InputDestCode'; Enter value for inputdestcode: DC1 old 7: WHERE x.DestCode = '&InputDestCode' new 7: WHERE x.DestCode = 'DC1' XML ----------------------------------------------- <Documents> <XMLDoc> <Node1>1-DC1-A</Node1> <Node2>1-DC1-B</Node2> </XMLDoc> <XMLDoc> <Node1>1-DC1-C</Node1> <Node2>1-DC1-D</Node2> </XMLDoc> </Documents>




Reply With Quote