使用where子句中的属性名从XML Clob中提取数据

Question

我们有一个名为JOURNALARTICLE的表，其中包含列CONTENT，其中的数据存储为CLOB。

<?xml version="1.0"?>
<root available-locales="en_US" default-locale="en_US">
    <dynamic-element name="group_html_title" type="text" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[Industrial AC Drives]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="group_image" type="document_library" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[/documents/20184/40787/Industrial-AC-Drives--Fan-and-Pump_Drives.png/8a1e761d-1cac-49aa-8212-fa3943204449?t=1437389273031]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="product_type" type="list" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[Drives]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="page_banner" type="list" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[a2cdb3ca-8872-4d08-a6ee-00b84495e0e0]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="access_groups" type="list" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="short_copy" type="text_area" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[<p>Drives that offer a broad range of control modes, features, options, and packaging, for use in factory automation processes and industrial OEM machines.&nbsp;<a href="javascript:void(0);" onclick="showDocumentsStaticPopup('eLM.J1000.01')" style="line-height: 1.5;">Test POPUP</a>&nbsp;<a href="javascript:void(0);" onclick="showDocumentsStaticPopup('eLM.J1000.01')" style="line-height: 1.5;">Test POPUP</a></p>]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="html_body" type="text_area" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="group_active" type="boolean" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[true]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="page_meta_description" type="text" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[Yaskawa's Family of AC Drives are designed for industrial automation applications. These adjustable frequency drives are typically capable of network communications, supported by software tools for parameter management and able to accept custom software]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="page_meta_keywords" type="text" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[Yaskawa, AC_drive, AC, VFD, industrial_automation, industrial_drives, variable_frequency_drive, AC_drives]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="business_weightage" type="ddm-number" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[5000]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="clickable_in_left_navigation" type="boolean" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[true]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="clickable_in_menu" type="boolean" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[true]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="visible_in_left_navigation" type="boolean" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[true]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="visible_in_menu" type="boolean" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[true]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="external_url" type="text" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[]]></dynamic-content>
    </dynamic-element>
    <dynamic-element name="shared_with_site" type="list" index-type="keyword" index="0">
        <dynamic-content language-id="en_US"><![CDATA[]]></dynamic-content>
    </dynamic-element>
</root>

我想从属性名称为"group_html_title"的dynamic-content节点中提取文本，因此它应该返回值"Industrial AC Drives“。

我该怎么做呢？

Answer 1

您可以使用XMLQuery或XMLTable：

select x.content
from journalarticle j
cross join xmltable ('/root/dynamic-element[@name="group_html_title"]'
  passing xmltype(j.content)
  columns content varchar2(4000) path 'dynamic-content'
) x;

CONTENT                                                                        
--------------------------------------------------------------------------------
Industrial AC Drives                                                            

XPath在root下查找dynamic-element节点，并将其限制为属性具有您想要的[@name=...]部分名称的节点。然后，它从任何匹配元素节点下的dynamic-content节点中提取节点值。

Read more in the documentation。

问题标题提到了where子句。您可以使用XMLTable将所有数据提取到列中，然后根据其中之一进行过滤，也可以直接在where子句中使用XMLQuery：

select *
from journalarticle j
where xmlquery('for $i in /root/dynamic-element[@name="group_html_title"]
  where $i/dynamic-content = "Industrial AC Drives"
  return $i'
  passing xmltype(j.content) returning content) is not null;

或者将两者结合使用，只需使用XMLTable获得所需的数据，并使用XMLQuery首先过滤基本表行。