如何从 DB2 Clob 检索数据

Question

我有一个带有 XML 的列 - XMLMESSAGE

CLOB 中的示例 XML ：

<xml>
 <attribute>name</attribute>
 <value>this is my xml</value>
 <logLocation>/user/apps/myLogFolder</logLocation>
 <engines>
    <engine>
        <engineName>Engine1</engineName>
        <engineLocation>$HOME/apps/engines</engineLocation>
        <engineVersion>3.1416</engineVersion>
    </engine>
    <engine>
        <engineName>Engine2</engineName>
        <engineLocation>$HOME/apps/engines/backup</engineLocation>
        <engineVersion>3b</engineVersion>
    </engine>
</engines>
<connections>
    <connection>
    <jdbc>jdbc:db2://127.0.0.1:50000/localdb</jdbc>
    <user>dbuser</user>
    <password>{}</password>
    </connection>
</connections>

我想检索值@ XPATH：/xml/engines/engine/engineName

早些时候，我在Oracle中使用XMLEXTRACT，并且想知道DB2是否具有类似的功能。我已经浏览了有关此主题的几个答案，但无法获得解决方案。

Answer 1

嗨，我

试图重现您的问题，我为 xml 节点添加了结束标记

CREATE table work.dd(int id, doc clob);
insert into work.DD VALUES (1,'<xml>
 <attribute>name</attribute>
 <value>this is my xml</value>
 <logLocation>/user/apps/myLogFolder</logLocation>
 <engines>
   <engine>
    <engineName>Engine1</engineName>
    <engineLocation>$HOME/apps/engines</engineLocation>
    <engineVersion>3.1416</engineVersion>
</engine>
<engine>
    <engineName>Engine2</engineName>
    <engineLocation>$HOME/apps/engines/backup</engineLocation>
    <engineVersion>3b</engineVersion>
</engine>
</engines>
  <connections>
     <connection>
     <jdbc>jdbc:db2://127.0.0.1:50000/localdb</jdbc>
     <user>dbuser</user>
    <password>{}</password>
    </connection>
</connections>
</xml>') 

那么你的查询应该像这样

select * from work.dd,
XMLTABLE(
    '$d/xml/engines/engine' PASSING XMLPARSE(DOCUMENT doc) AS "d"
    COLUMNS
    engineName varCHAR(100) PATH 'engineName'
)

我在 lUW 10.1 中对其进行了测试