时间戳

首页 > 解决方案 > 在从数据库选择中检索 xmltype 时需要帮助

sql - 在从数据库选择中检索 xmltype 时需要帮助

问题描述

我有以下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 和 Type。

我想要实现的目标如下：

<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 数据库选择查询中实现这一点？我不想创建存储过程并希望在选择查询中实现这一点。

标签： sqldatabaseoracle

解决方案

首先，请不要使用Oracle保留字（“date”、“case”、“type”）作为列名！对每个列名进行双引号会让人厌烦。

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

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

另外，我认为您的“/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>

推荐阅读