Oracle 12c中如何通过XMLTYPE提取XML根元素的属性值？

环境说明 #

当前使用的Oracle版本信息：

SELECT * FROM v$version;

返回结果：
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

现有查询语句 #

你目前使用的包含XML数据的查询如下：

with t(xml) as ( 
select xmltype( 
'<SSO_XML xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" TimeStamp="2020-08-05T21:57:23Z" Target="Production" Version="1.0" TransactionIdentifier="PLAN_A" SequenceNmbr="123456" xmlns="http://www.w3.org/2001/XMLSchema"> 
<PlanCode PlanCodeCode="CHOICE"> 
<S_DAYS FARE="10" Start="2020-08-07" End="2020-10-30" Mon="true" Tue="true" Weds="true" Thur="true" Fri="true" Sat="true" Sun="true"> 
<STUDENT> 
<DIVISION ORIGINAL="150.05" Code="Flat" S_CODE="1" /> 
<DIVISION ORIGINAL="150.05" Code="Flat" S_CODE="2" /> 
</STUDENT> 
</S_DAYS> 
</PlanCode> 
</SSO_XML>') from dual 
) 
select h.PlanCodeCode ,b.Original ,b.code ,b.s_code 
from t 
cross join xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'), 
'/SSO_XML' passing t.xml 
columns PlanCodeCode varchar2(100) path './PlanCode/@PlanCodeCode', 
attributes xmltype path './PlanCode' 
) h 
cross join xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'), 
'PlanCode/S_DAYS/STUDENT/DIVISION' passing h.attributes 
columns ORIGINAL number path '@ORIGINAL', 
Code varchar2(100) path '@Code', 
S_CODE number path '@S_CODE' 
) b;

需求实现方案 #

完全可以在现有查询基础上直接扩展，不需要重新编写新语句。核心思路是：第一个xmltable已经基于根节点/SSO_XML进行解析，所以直接在它的columns部分新增对应根属性的提取规则即可。

修改后的完整查询 #

with t(xml) as ( 
select xmltype( 
'<SSO_XML xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" TimeStamp="2020-08-05T21:57:23Z" Target="Production" Version="1.0" TransactionIdentifier="PLAN_A" SequenceNmbr="123456" xmlns="http://www.w3.org/2001/XMLSchema"> 
<PlanCode PlanCodeCode="CHOICE"> 
<S_DAYS FARE="10" Start="2020-08-07" End="2020-10-30" Mon="true" Tue="true" Weds="true" Thur="true" Fri="true" Sat="true" Sun="true"> 
<STUDENT> 
<DIVISION ORIGINAL="150.05" Code="Flat" S_CODE="1" /> 
<DIVISION ORIGINAL="150.05" Code="Flat" S_CODE="2" /> 
</STUDENT> 
</S_DAYS> 
</PlanCode> 
</SSO_XML>') from dual 
) 
select 
  h.TimeStamp,
  h.Target,
  h.Version,
  h.TransactionIdentifier,
  h.SequenceNmbr,
  h.PlanCodeCode,
  b.Original,
  b.code,
  b.s_code 
from t 
cross join xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'), 
'/SSO_XML' passing t.xml 
columns 
  -- 新增根元素属性提取
  TimeStamp varchar2(50) path '@TimeStamp',
  Target varchar2(100) path '@Target',
  Version varchar2(20) path '@Version',
  TransactionIdentifier varchar2(100) path '@TransactionIdentifier',
  SequenceNmbr varchar2(20) path '@SequenceNmbr',
  -- 原有列保留
  PlanCodeCode varchar2(100) path './PlanCode/@PlanCodeCode', 
  attributes xmltype path './PlanCode' 
) h 
cross join xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'), 
'PlanCode/S_DAYS/STUDENT/DIVISION' passing h.attributes 
columns ORIGINAL number path '@ORIGINAL', 
Code varchar2(100) path '@Code', 
S_CODE number path '@S_CODE' 
) b;

关键修改说明 #

• 在第一个xmltable的columns块中，新增了对应根属性的列定义，路径使用@属性名（比如@TimeStamp），因为当前解析上下文就是根节点SSO_XML，可以直接定位到这些属性。
• 在SELECT子句中，把新增的根属性列加入到查询结果里，这样就能同时获取原来的业务数据和根元素的元数据属性。

这样修改后，查询结果会包含你需要的所有根属性值，完全复用了原有查询的结构，不需要额外的逻辑处理。

内容的提问来源于stack exchange，提问作者ajmalmhd04