Aggregate values of XML tags via SQL query

Question

I need to aggregate some sub-tag values into a Oracle SQL column.

Let's say the XML structure is something like this:

<Product>
[..other tags..]
 <Attributes>
   <Statements Id="1" Name="Statement 0">
     <Statement Id="4">Subtype 1</Statement>
   </Statements>
   <Statements Id="3" Name="Statement 1">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="15">Subtype 15</Statement>
   </Statements>
   <Statements Id="16" Name="Statement 2">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="10">Subtype 10</Statement>
   </Statements>
 </Attributes>
</Product>

My Product table in SQL should be populated with something like this: Statement_2 with entry Subtype4,Subtype5,Subtype10... etc..

I actually tried to use some query like the following:

SELECT 
    b.id,
    LISTAGG(x2.statement_1, ',') WITHIN GROUP (ORDER BY NULL) AS statement 1,
    LISTAGG(y2.statement_2, ',') WITHIN GROUP (ORDER BY NULL) AS statement_2
FROM 
    TABLE_WHERE_XML_RESIDES b

CROSS JOIN 
    XMLTABLE('/root_path/Statements[@Id="3"]' 
             PASSING XMLTYPE(b.XML_DOCUMENT)
             COLUMNS statement1_list XMLTYPE PATH 'Statement') x
CROSS JOIN 
    XMLTABLE('/Statement' 
             PASSING x.statement1_list 
             COLUMNS statement_name_1 VARCHAR2(100) PATH 'text()') x2

CROSS JOIN 
   XMLTABLE('/root_path/Statements[@Id="16"]' 
             PASSING XMLTYPE(b.XML_DOCUMENT)
             COLUMNS statement1_list XMLTYPE PATH 'Statement') y

CROSS JOIN 
    XMLTABLE('/Statement' 
             PASSING x.statement2_list 
             COLUMNS statement_name_1 VARCHAR2(4000) PATH 'text()') y2
GROUP BY 
    b.id;

In this way the aggregation kind of of works, but for many <Statements> the query will be not performant I guess. And main problem is, due to the join mechanisms subsequent statements values (i.e. statements 2) would be duplicated for N times (which correspond to the N entries already retrieved for the column in statement1.

Answer 1

You can use or operator in the XPath expression and use conditional aggregation to aggregate each Id within a single parse:

SELECT b.id,
       x.*
FROM   TABLE_WHERE_XML_RESIDES b
       CROSS JOIN LATERAL (
         SELECT LISTAGG(CASE id WHEN  3 THEN value END, ', ') AS statement3,
                LISTAGG(CASE id WHEN 16 THEN value END, ', ') AS statement16
         FROM   XMLTABLE(
                  '/Product/Attributes/Statements[@Id="3" or @Id=16]/Statement' 
                  PASSING XMLTYPE(b.XML_DOCUMENT)
                  COLUMNS 
                    id    NUMBER       PATH './../@Id',
                    value VARCHAR2(20) PATH './text()'
                  ) x
       ) x;

Which, for the sample data:

CREATE TABLE TABLE_WHERE_XML_RESIDES (id, xml_document) AS
SELECT 1, EMPTY_CLOB() || '<Product>
 <Attributes>
   <Statements Id="1" Name="Statement 0">
     <Statement Id="4">Subtype 1</Statement>
   </Statements>
   <Statements Id="3" Name="Statement 1">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="15">Subtype 15</Statement>
   </Statements>
   <Statements Id="16" Name="Statement 2">
     <Statement Id="4">Subtype 4</Statement>
     <Statement Id="5">Subtype 5</Statement>
     <Statement Id="10">Subtype 10</Statement>
   </Statements>
 </Attributes>
</Product>' FROM DUAL;

Outputs:

ID	STATEMENT3	STATEMENT16
1	Subtype 4, Subtype 5, Subtype 15	Subtype 4, Subtype 5, Subtype 10

fiddle