oracle - Oracle XML 表解析不获取没有子节点的节点
问题描述
我正在运行下面的 sql 来解析 XML,但只有当子节点存在时它才返回父节点。
select
nvxml.catalogs,nvlvl.levels L1,nvlvl.L1_CAT_ID,lvl2.l2 , lvl2.L2_CAT_ID
--, nvlvl.L1_CAT_ID ,lvl2.L2, lvl2.l2_cat_id
from
XMLTABLE('//category' PASSING XMLTYPE.CREATEXML (
'<categories>
<category name="Business Services">
<L1 name="Mail, Messenger and Delivery" categoryID="BSSRVMMDSV" />
<L1 name="Office Supplies" categoryID="BSSRVOFFSP" />
<L1 name="Translation & Interpretation" categoryID="BSSRVLNGSP" />
</category>
<category name="Telecommunications">
<L1 name="Mobile and Service Plans" categoryID="TLCOMHHWDB">
<L2 name="Mobile - Upgrade" categoryID="WHHDDBBDU" />
<L2 name="New Mobile and Plans" categoryID="PDAS_TMBB8820OWHHDDNBBDP" />
<L2 name="Mobile - Repair" categoryID="WHHDDBBDR" />
<L2 name="Mobile Access to Notes" categoryID="WHHDDBBNACC" />
<L2 name="Mobile Plan - Change" categoryID="WHHDDBBPC" />
<L2 name="Mobile Service - Cancel" categoryID="WHHDDBBSC" />
</L1>
</category>
</categories>'
)
COLUMNS catalogs VARCHAR2(300) PATH '@name' ,
levels XMLTYPE PATH '//L1' ,
levels2 XMLTYPE PATH '//L1//L2') nvxml ,
XMLTABLE('//L1' passing nvxml.levels
columns
-- levels2 XMLTYPE PATH '//L2' ,
levels varchar2(300) PATH '@name',
L1_CAT_ID VARCHAR2(300) PATH '@categoryID' ) nvlvl ,
XMLTABLE ('//L2' passing nvxml.levels2
columns
L2 varchar2(300) PATH '@name',
L2_CAT_ID VARCHAR2(300) PATH '@categoryID') lvl2
我应该怎么做才能获得没有子 L2 的 L1 值?
解决方案
为此,您需要将查询转换为使用 ANSI 连接语法,以便您可以对 lvl2 xmltable 执行 LEFT OUTER JOIN,如下所示:
SELECT nvxml.catalogs,
nvlvl.levels l1,
nvlvl.l1_cat_id,
lvl2.l2,
lvl2.l2_cat_id
--, nvlvl.L1_CAT_ID ,lvl2.L2, lvl2.l2_cat_id
FROM xmltable('//category' passing XMLTYPE('<categories>
<category name="Business Services">
<L1 name="Mail, Messenger and Delivery" categoryID="BSSRVMMDSV" />
<L1 name="Office Supplies" categoryID="BSSRVOFFSP" />
<L1 name="Translation & Interpretation" categoryID="BSSRVLNGSP" />
</category>
<category name="Telecommunications">
<L1 name="Mobile and Service Plans" categoryID="TLCOMHHWDB">
<L2 name="Mobile - Upgrade" categoryID="WHHDDBBDU" />
<L2 name="New Mobile and Plans" categoryID="PDAS_TMBB8820OWHHDDNBBDP" />
<L2 name="Mobile - Repair" categoryID="WHHDDBBDR" />
<L2 name="Mobile Access to Notes" categoryID="WHHDDBBNACC" />
<L2 name="Mobile Plan - Change" categoryID="WHHDDBBPC" />
<L2 name="Mobile Service - Cancel" categoryID="WHHDDBBSC" />
</L1>
</category>
</categories>')
columns catalogs VARCHAR2(300) path '@name',
levels xmltype path '//L1',
levels2 xmltype path '//L1//L2') nvxml
CROSS JOIN
xmltable('//L1' passing nvxml.levels columns
-- levels2 XMLTYPE PATH '//L2' ,
levels VARCHAR2(300) path '@name',
l1_cat_id VARCHAR2(300) path '@categoryID') nvlvl
LEFT OUTER JOIN xmltable('//L2' passing nvxml.levels2 columns l2 VARCHAR2(300) path '@name', l2_cat_id VARCHAR2(300) path '@categoryID') lvl2 ON 1=1;
CATALOGS L1 L1_CAT_ID L2 L2_CAT_ID
-------------------- ---------------------------------------- ------------ ------------------------- ----------------------------
Business Services Mail, Messenger and Delivery BSSRVMMDSV
Business Services Office Supplies BSSRVOFFSP
Business Services Translation & Interpretation BSSRVLNGSP
Telecommunications Mobile and Service Plans TLCOMHHWDB Mobile - Upgrade WHHDDBBDU
Telecommunications Mobile and Service Plans TLCOMHHWDB New Mobile and Plans PDAS_TMBB8820OWHHDDNBBDP
Telecommunications Mobile and Service Plans TLCOMHHWDB Mobile - Repair WHHDDBBDR
Telecommunications Mobile and Service Plans TLCOMHHWDB Mobile Access to Notes WHHDDBBNACC
Telecommunications Mobile and Service Plans TLCOMHHWDB Mobile Plan - Change WHHDDBBPC
Telecommunications Mobile and Service Plans TLCOMHHWDB Mobile Service - Cancel WHHDDBBSC
推荐阅读
- java - Spring Bean 配置总是返回 null
- arrays - 使用角度 5 转换动态值
- swift - 动画多个原型表视图单元格
- python - 报告实验室的画布对象的正确构造方法是什么?
- ruby-on-rails - 在 Windows 上运行的 Ubuntu bash 上设置 PostgreSQL
- javascript - 赛普拉斯调用不执行隐藏元素交互的“显示”方法
- csv - 使用 p:dataexporter 导出 csv
- r - 计算df中每个元素的百分比值(基于最大值);但是在指定的列中
- java - 每次我必须改变一些东西来反映
- android - 使用 Flutter/Dart 构建的应用空间