-
Merging XML Document
Hi everyone,
I have a table with following structure
SrcCode Char(5),
DestCode Char(5),
XMLDoc XMLType.
I have a requirement to merge all the XML documents with same destCode under one parent tag.
The contents of XMLDoc column is:
HTML Code:
<XMLDoc>
<Node1> </Node1>
<Node2> </Node2>
</XMLDoc>
I want the result in following format
HTML Code:
<Documents>
<XMLDoc></XMLDoc>
<XMLDoc></XMLDoc>
</Documents>
I try the following query but it does not provide me required results
Select XMLElement("ParentNode", XMLDoc)
from XMLDocs;
but the query returns like
HTML Code:
<Documents> <XMLDoc></XMLDoc> </Documents>
<Documents> <XMLDoc></XMLDoc> </Documents>
-
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>
-
