需要帮助从数据库选择中检索 xmltype

Question

我有以下数据库表CASH，有 5 列：

REGISTER    DATE    CASE    BAG TYPE
1234    24-SEP-18   1123    112 A
1234    24-SEP-18   1124    113 S
1234    24-SEP-18   1123    116 S
1234    24-SEP-18   1124    117 A
7895    24-SEP-18   2568    119 A
7895    24-SEP-18   2568    118 S

其中收银机编号是收银机，可以有多个CASE链接到它，每个CASE可以附加多个BAG和类型。

我想要实现的目标如下：

<ROOT>
<REGISTERS>
<REGISTER>1234</REGISTER>
<DATE>24-SEP-2018</DATE>
<DETAILS>
<BAG>1123</BAG>
<DETAIl>
<BAG>112</BAG>
<TYPE>A</TYPE>
</DETAIl>
<DETAIl>
<BAG>116</BAG>
<TYPE>S</TYPE>
</DETAIl>
</DETAILS>
<DETAILS>
<BAG>1124</BAG>
<DETAIl>
<BAG>113</BAG>
<TYPE>S</TYPE>
</DETAIl>
<DETAIl>
<BAG>117</BAG>
<TYPE>A</TYPE>
</DETAIl>
</DETAILS>
</REGISTERS>
<REGISTERS>
<REGISTER>7895</REGISTER>
<DATE>24-SEP-2018</DATE>
<DETAILS>
<BAG>2568</BAG>
<DETAIl>
<BAG>119</BAG>
<TYPE>A</TYPE>
</DETAIl>
<DETAIl>
<BAG>118</BAG>
<TYPE>S</TYPE>
</DETAIl>
</DETAILS>
</REGISTERS>
</ROOT>

您能否给出一些指示，如何在 Oracle 数据库选择查询中实现这一点？我不想创建存储过程，并希望在选择查询中实现此目的。

Answer 1

首先，请不要使用 Oracle 保留字("日期"、"大小写"、"类型"(作为列名！双引号每个列名称会很烦人。

所以，这是一个相当复杂的场景。您正在尝试对同一个表执行 3 个级别的聚合。

此外，Oracle 的 XML 函数为您提供了在查询中动态构建 XML 的有限能力。据我所知，在 XML 层次结构的同一级别混合列 (xmlelement( 和行 (xmlagg( 数据的唯一方法是使用xmlelement构造函数连接它们。

另外，我假设您的"/ROOT/REGISTERS/DETAILS/BAG"标签是一个错字，而您的意思是"CASE"。

但我认为这就是你的要求。

create table cash (register number, "DATE" date, "CASE" number, bag number, "TYPE" varchar2(1));
insert into cash values (1234, to_date('09/24/2018', 'mm/dd/yyyy'), 1123, 112, 'A');
insert into cash values (1234, to_date('09/24/2018', 'mm/dd/yyyy'), 1124, 113, 'S');
insert into cash values (1234, to_date('09/24/2018', 'mm/dd/yyyy'), 1123, 116, 'S');
insert into cash values (1234, to_date('09/24/2018', 'mm/dd/yyyy'), 1124, 117, 'A');
insert into cash values (7895, to_date('09/24/2018', 'mm/dd/yyyy'), 2568, 119, 'A');
insert into cash values (7895, to_date('09/24/2018', 'mm/dd/yyyy'), 2568, 118, 'S');
select xmlelement("ROOT",
xmlagg(xmlelement("REGISTERS",
xmlelement("REGISTER", t1.register), 
xmlelement("DATE", t1."DATE"),
(select xmlagg(xmlelement("DETAILS",
xmlelement("CASE", t2."CASE"),
(select xmlagg(xmlelement("DETAIL",
xmlforest(t3.bag, t3."TYPE"))
)
from (select bag, "TYPE" from cash 
where cash."REGISTER" = t2."REGISTER" and cash."DATE" = t2."DATE"
and cash."CASE" = t2."CASE") t3)
))
from (select distinct register, "DATE", "CASE" from cash
where cash."REGISTER" = t1."REGISTER" and cash."DATE" = t1."DATE") t2
)
)  
)
) as xml_data
from (select distinct register, "DATE" from cash) t1
;

输出：

<ROOT>
<REGISTERS>
<REGISTER>1234</REGISTER>
<DATE>2018-09-24</DATE>
<DETAILS>
<CASE>1123</CASE>
<DETAIL>
<BAG>112</BAG>
<TYPE>A</TYPE>
</DETAIL>
<DETAIL>
<BAG>116</BAG>
<TYPE>S</TYPE>
</DETAIL>
</DETAILS>
<DETAILS>
<CASE>1124</CASE>
<DETAIL>
<BAG>113</BAG>
<TYPE>S</TYPE>
</DETAIL>
<DETAIL>
<BAG>117</BAG>
<TYPE>A</TYPE>
</DETAIL>
</DETAILS>
</REGISTERS>
<REGISTERS>
<REGISTER>7895</REGISTER>
<DATE>2018-09-24</DATE>
<DETAILS>
<CASE>2568</CASE>
<DETAIL>
<BAG>119</BAG>
<TYPE>A</TYPE>
</DETAIL>
<DETAIL>
<BAG>118</BAG>
<TYPE>S</TYPE>
</DETAIL>
</DETAILS>
</REGISTERS>
</ROOT>