xml - oracle中xmlconcat函数的使用方法
问题描述
我想使用 xmlconcat 函数,以便我的 xml 如下所示:
<BusProcess>
<CRR>
<CUSIP>65443W404</CUSIP>
<RUP>100</RUP>
<ACB>2500</ACB>
</CRR>
<LEI>546</LEI>
<AddModCDIC>
<CDICData>
<UCIData>
<UCI>BMO2100001200001</UCI>
<InterestlnDeposit>50</InterestlnDeposit>
</UCIData>
<UCIData>
<UCI>BMO2100005500012</UCI>
<InterestlnDeposit>50</InterestlnDeposit>
</UCIData>
</CDICData>
</AddModCDIC>
</BusProcess>
有人可以帮助调试我的代码以获得上述结果
select XMLELEMENT ("BusProcess",XMLFOREST(
XMLFOREST('65443W404' as "CUSIP",100 as "RUP", 2500 as "ACB") as "CRR",
(CASE WHEN 1=1 THEN XMLCONCAT (xmlelement ("LEI",a.vcud_lei ),XMLELEMENT("CDICData",
XMLAGG( XMLELEMENT("UCIData",xmlforest(a.vcud_uci as "UCI",a.vcud_per as "InterestlnDeposit")))))
END ) as "AddModCDIC") )
from vw_cdic_uci_detail a
where vcud_clcode in ('21000055','21000012');
解决方案
WITH vw_cdic_uci_detail AS ( SELECT 546 AS vcud_lei, 'BMO2100001200001' AS vcud_uci, 50 AS vcud_per FROM DUAL UNION ALL
SELECT 546 AS vcud_lei, 'BMO2100005500012' AS vcud_uci, 50 AS vcud_per FROM DUAL
)
SELECT XMLELEMENT( "BusProcess",
XMLFOREST(
XMLFOREST( '65443W404' AS "CUSIP",
100 AS "RUP",
2500 AS "ACB"
) AS "CRR"
),
XMLELEMENT("LEI", vcud_lei),
XMLCONCAT( AddModCDIC ) AS "AddModCDIC"
)
FROM ( SELECT vcud_lei,
CASE
WHEN 1=1
THEN
XMLCONCAT(
XMLELEMENT("AddModCDIC",
XMLELEMENT("CDICData",
XMLAGG(
XMLELEMENT("UCIData",
XMLFOREST(vcud_uci AS "UCI",
vcud_per AS "InterestlnDeposit"
)
)
)
)
)
)
ELSE NULL
END AS AddModCDIC
FROM vw_cdic_uci_detail
GROUP BY vcud_lei
) a;
推荐阅读
- javascript - 多个加载指示器显示在彼此上方
- sql-server - 工作组上运行的高可用性集群系统中的 Sql Server 复制设置
- ios - Cocoapod 系统设置问题 - 'pod install' 出错
- c++ - CMake 无法在 Clion 中链接 Boost 正则表达式库
- css - Select2 - 我们可以设置下拉菜单以匹配页面其余部分的样式吗?
- arrays - 如何控制在 Google 表格中触发多少个单元格
- ios - 未调用委托函数
- javascript - 使用javascript单击突出显示的元素
- reactjs - 我无法在 Azure Linux 环境中仅部署 ReactJS 应用程序的构建文件夹
- python - Pandas Out of Bounds 时间戳解决方法