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:
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>
The query you need:
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>