問題描述

我在數據庫屬性object_body"中有以下 CLOB XML 片段:

<object stamp="8fc630bd-f1f5-43ac-b9d2-3b9db9054c75_1481811010527" type="Aggregation" version="12">
  <property name="name" value="COA_Cash_Position" valueType="string"/>
  <property name="description" value="COA_Cash_Position" valueType="string"/>
  <property name="objectId" value="ef4296f1-6af6-4de8-83fe-fa01cf327d87" valueType="string"/>
  <property name="branchId" value="01ecc9ed-27f3-42cb-a44d-2cec221ec8fa" valueType="string"/>
  <property name="models" valueType="table">
    <object type="ModelBasedTask:modelEntry" version="3">
      <property name="dataModel" valueType="url">DataModel["ModifyModel[COA_Cash_Positions_BHS_Unadjusted].STAGING:X_NRS_BSL40K_BHS"{ModifyModel[ff81e5f2-2ef6-437a-b113-67f7c0dd5e53].cdf308fc-520d-4611-a8db-4e6863e216b8:9ffcd08e-732a-4270-b15f-f4775bcc69db}]</property>
      <property name="instanceSelectionRule" valueType="object">
        <object type="InstanceSelectionRule" version="3">
          <property name="instanceDateRule" valueType="object">
            <object type="InstanceSelectionRule:rule" version="3">
              <property name="type" value="EQUAL" valueType="string"/>
            </object>
          </property>
          <property name="instanceKeyRules" valueType="table"/>
        </object>
      </property>
    </object>
  </property>
</object>

目的是提取name="description"的屬性標簽的值
=> 生成COA_Cash_Position"

Aim is to extract the value of the property tag with name="description"
=> to produce "COA_Cash_Position"

嘗試了以下所有變體:

EXTRACTVALUE(xmltype(object.object_body), '/object/property[@name="description"].getStringVal()'),

如果我丟失了 .getStringVal() 查詢有效，但顯然只提供空結果(因為屬性標簽本身不包含任何值).

If I loose the .getStringVal() the query works but obviously only provides empty results (as the property tag itself doesn't include any value).

獲取大部分錯誤消息，例如:

Get mostly error messages the likes of:

ORA-31011:XML 解析失敗ORA-19202: XML 處理出錯LPX-00601:無效令牌:'/object/property[@name="description"].getStringVal()'

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00601: Invalid token in: '/object/property[@name="description"]. getStringVal()'

最簡單的方法是提取兩個 xml 標記之間的值，但獲取特定的屬性值(例如這里的 name="description" 標記的值") 似乎規避了 XML 提取的可能性.

What easily works is extracting the values between two xml tags but getting a specific property value (like here the "value" of the tag with name="description") seems to evade the XML extract possibilities.

熱烈感謝任何幫助...

Any help is warmly appreciated...

推薦答案

EXTRACTVALUE 在 Oracle 12 中已棄用 - 它正在被 XMLTABLE 或 XMLQUERY.

SELECT x.description
FROM   your_table t
       CROSS JOIN
       XMLTABLE(
         '//object/'
         PASSING XMLTYPE( t.your_clob_column )
         COLUMNS description VARCHAR2(4000) PATH './property[@name="description"]/@value'
       ) x;

或

SELECT XMLQUERY(
         '//object/property[@name="description"]/@value'
         PASSING XMLTYPE( your_clob_column )
         RETURNING CONTENT
       ).getStringVal()
FROM   your_table;

但是如果你確實想使用 EXTRACTVALUE 你可以這樣做:

But if you do want to use EXTRACTVALUE you can do:

SELECT EXTRACTVALUE(
         XMLTYPE( your_clob_column ),
         '//object/property[@name="description"]/@value'
       )
FROM   your_table;

這篇關于通過 SQL 從 Oracle 中的 clob 中提取 XML 子標簽的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！