正在获取存储在varchar2列中的XML标记数据

Question

我有一个如下表：

ID  Name      Roll         Data
1   ABC        12       <ngc_mid_nu_3><tid_nu><status>A</status> 
<term_no>03</term_no> 
</tid_nu><tid_nu><status>A</status> 
<term_no>04</term_no> 
</tid_nu><ngc_mid_nu_3>
2    XYZ        32       <ngc_mid_nu_3><tid_nu><status>B</status> 
<term_no>08</term_no> 
</tid_nu><tid_nu><status>A</status> 
<term_no>04</term_no> 
</tid_nu><ngc_mid_nu_3>

所有列都是varchar2，包括名为Data的列。

我想为每一行获取<status>标记和<term_no>标记中的值。我该怎么做？

Answer 1

您可以将表行与XMLTable交叉连接，XMLTable提取您想要的所有字段：

select t.id, t.name, t.roll, x.status, x.term_no
from your_table t
cross join xmltable ('/ngc_mid_nu_3/tid_nu'
passing xmltype(t.data)
columns status varchar2(10) path 'status',
term_no varchar2(10) path 'term_no'
) x;

为您期望真正看到的数据适当地设置columns数据类型和大小。

在CTE中使用示例数据进行演示(在XML中使用固定的结束标记，即</ngc_mid_nu_3>而不是<ngc_mid_nu_3>(：

with your_table (ID, Name, Roll, Data) as (
select 1, 'ABC', 12, '<ngc_mid_nu_3><tid_nu><status>A</status> 
<term_no>03</term_no> 
</tid_nu><tid_nu><status>A</status> 
<term_no>04</term_no> 
</tid_nu></ngc_mid_nu_3>' from dual
union all
select 2, 'XYZ', 32, '<ngc_mid_nu_3><tid_nu><status>B</status> 
<term_no>08</term_no> 
</tid_nu><tid_nu><status>A</status> 
<term_no>04</term_no> 
</tid_nu></ngc_mid_nu_3>' from dual
)
select t.id, t.name, t.roll, x.status, x.term_no
from your_table t
cross join xmltable ('/ngc_mid_nu_3/tid_nu'
passing xmltype(t.data)
columns status varchar2(10) path 'status',
term_no varchar2(10) path 'term_no'
) x;
ID NAME             ROLL STATUS     TERM_NO   
---------- ---------- ---------- ---------- ----------
1 ABC                12 A          03        
1 ABC                12 A          04        
2 XYZ                32 B          08        
2 XYZ                32 A          04        

如果实际的XML字符串格式错误——在最后的结束标记中缺少斜杠——那么您需要首先修复它。