如何在 Oracle 查询中从 XML 字段中选择值

debugcn 发表于 Dev

阿舒托什

我有来自我的表列之一的 XML 数据，其示例 XML 格式如下所述：

<?xml version="1.0" encoding="Big5"?>
<SN Name="Group Medical Member Setup">
  <DO Name="datPrlMmr" Label="" Table="COMPRLMMR">
    <RECORD>
      <ACTION Name="M">
    <F Name="MMRCNYOFRSN" Label="" OldValue="HKG" NewValue="AUS" />
    <F Name="MDFBY" Label=" " OldValue="fc" NewValue="admin" />
    <F Name="MDFTMSTP" Label=" " OldValue="Thu Feb 19 11:14:37 HKT 2004" NewValue="Tue May 07 14:24:09 HKT 2019" />
      </ACTION>
    </RECORD>
  </DO>
  <DO Name="doAdmGMPlyMmrDtl" Label="" Table="ADMGMPLYMMRDTL">
    <RECORD>
      <ACTION Name="M">
    <F Name="MDFTMSTP" Label="Modified TimeStamp" OldValue="Mon May 06 03:12:20 SGT 2019" NewValue="Mon May 06 03:15:00 SGT 2019" />
    <F Name="RCDTMSTP" Label="Record TimeStamp" OldValue="Mon May 06 03:12:20 SGT 2019" NewValue="Mon May 06 03:15:00 SGT 2019" />
      </ACTION>
    </RECORD>
  </DO>
</SN>

我想提取'NewValue'与字段标记名对应的值Name="MMRCNYOFRSN"。这只会在 XML 中出现一次。如何NewValue通过 SQL Query for Oracle提取(AUS)？

亚历克斯·普尔

由于您只需要一个（属性）值，您也可以使用XMLQuery执行此操作：

-- CTE for your sample data
with your_table (your_column) as (
  select xmltype('<?xml version="1.0" encoding="Big5"?>
<SN Name="Group Medical Member Setup">
  <DO Name="datPrlMmr" Label="" Table="COMPRLMMR">
    <RECORD>
      <ACTION Name="M">
    <F Name="MMRCNYOFRSN" Label="" OldValue="HKG" NewValue="AUS" />
    <F Name="MDFBY" Label=" " OldValue="fc" NewValue="admin" />
    <F Name="MDFTMSTP" Label=" " OldValue="Thu Feb 19 11:14:37 HKT 2004" NewValue="Tue May 07 14:24:09 HKT 2019" />
      </ACTION>
    </RECORD>
  </DO>
  <DO Name="doAdmGMPlyMmrDtl" Label="" Table="ADMGMPLYMMRDTL">
    <RECORD>
      <ACTION Name="M">
    <F Name="MDFTMSTP" Label="Modified TimeStamp" OldValue="Mon May 06 03:12:20 SGT 2019" NewValue="Mon May 06 03:15:00 SGT 2019" />
    <F Name="RCDTMSTP" Label="Record TimeStamp" OldValue="Mon May 06 03:12:20 SGT 2019" NewValue="Mon May 06 03:15:00 SGT 2019" />
      </ACTION>
    </RECORD>
  </DO>
</SN>')
  from dual
)
-- actual query
select xmlquery('/SN/DO/RECORD/ACTION/F[@Name="MMRCNYOFRSN"]/@NewValue'
  passing t.your_column
  returning content) as MMRCNYOFRSN
from your_table t;

MMRCNYOFRSN                                                                     
--------------------------------------------------------------------------------
AUS

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。