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.
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 |