查询包含XML消息的CLOB列

Question

我在尝试数据库表格。该表包含使用CLOB数据类型存储xml消息的列。在XML的消息的结构如下：

<t:MsgNameXML typeInfo="source" xsi:schemaLocation="http://example/location FileName.xsd" xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"  > 
    <t:info1>data1</t:info1> 
    <t:info2>data2</t:info2> 
    <t:Section> 
     <t:SubSection> 
      <t:SubSubSection> 
       <t:variable1>data3</t:variable1> 
       <t:variable2>data4</t:variable2> 
      </t:SubSubSection> 
     </t:SubSection> 
    </t:Section> 
    <t:Section> 
     <t:SubSection> 
      <t:SubSubSection> 
       <t:variable1>data5</t:variable1> 
       <t:variable2>data6</t:variable2> 
      </t:SubSubSection> 
     </t:SubSection> 
    </t:Section> 
</t:MsgNameXML> 

所以，至少对我来说，主要的困难是命名空间和事实有没有多个节点。

我希望能得到下面的输出（含上述XML记录）：

INFO1 INFO2 VARIABLE1 VARIABLE2 
------------------------------------- 
data1 data2 data3  data4 
data1 data2 data5  data6 

我已经构造下面的查询来尝试提取“变量1”（Oracle数据库，那么PL SQL，我猜的）：

select 
extractvalue(xmltype(CLOB_COLUMN_NAME), 
'/t:MsgNameXML/t:Section/t:SubSection/t:SubSubSection/t:variable1', 
'xmlns:t="xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 

from 
TABLE_NAME, 
TABLE(XMLSEQUENCE(EXTRACT(CLOB_COLUMN_NAME, '/t:MsgNameXML/t:Section/t:SubSection/t:SubSubSection/t:variable1'))) 

这不幸的是产生以下错误消息：

ORA-00932: inconsistent datatypes: expected - got - 
00932. 00000 - "inconsistent datatypes: expected %s got %s" 
*Cause:  
*Action: 
Error at Line: 9 Column: 33 

（第9行第33列指的是CLOB_COLUMN_NAME在from子句中的EXTRACT函数之后的位置）。

有没有人知道我做错了什么，以及我该如何纠正？

此外，我需要扩展此查询以从XML消息中提取更多的值，而不仅仅是“variable1”（请参阅​​上面的所需结果）。任何帮助，将不胜感激。

感谢你的帮助，

汤姆

Answer 1

您的ORA-00932的直接原因是，你想直接从CLOB提取;你将在一个地方了，但这样的：

TABLE(XMLSEQUENCE(EXTRACT(CLOB_COLUMN_NAME, ...))) 

...将需要：

TABLE(XMLSEQUENCE(EXTRACT(XMLType(CLOB_COLUMN_NAME), ...))) 

但命名空间仍不能在该条款中公认的，而且你得到ORA-31011如果你只是改变这一点。通过改变提取回来的水平一下子

select extractvalue(column_value, '/t:variable1', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as variable1 
from 
TABLE_NAME, 
TABLE(XMLSEQUENCE(EXTRACT(XMLType(CLOB_COLUMN_NAME), 
    '/t:MsgNameXML/t:Section/t:SubSection/t:SubSubSection/t:variable1', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"'))); 

VARIABLE1 
---------- 
data3  
data5  

或者两个变量：

select extractvalue(column_value, 't:SubSubSection/t:variable1', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as variable1, 
    extractvalue(column_value, 't:SubSubSection/t:variable2', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as variable2 
from 
TABLE_NAME, 
TABLE(XMLSEQUENCE(EXTRACT(XMLType(CLOB_COLUMN_NAME), 
    '/t:MsgNameXML/t:Section/t:SubSection/t:SubSubSection', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"'))); 

VARIABLE1 VARIABLE2 
---------- ---------- 
data3  data4  
data5  data6  

您可以通过在EXTRACT调用的命名空间为好，并纠正了错字得到的东西包括信息值，以及将会使问题更加复杂和重复的：

select extractvalue(XMLType(CLOB_COLUMN_NAME), 't:MsgNameXML/t:info1', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as info1, 
    extractvalue(XMLType(CLOB_COLUMN_NAME), 't:MsgNameXML/t:info2', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as info2, 
    extractvalue(column_value, 't:SubSubSection/t:variable1', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as variable1, 
    extractvalue(column_value, 't:SubSubSection/t:variable2', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"') 
    as variable2 
from 
TABLE_NAME, 
TABLE(XMLSEQUENCE(EXTRACT(XMLType(CLOB_COLUMN_NAME), 
    '/t:MsgNameXML/t:Section/t:SubSection/t:SubSubSection', 
    'xmlns:t="http://url/of/custom/namespace" xmlns:"http://url/of/default/namespace"'))); 

INFO1  INFO2  VARIABLE1 VARIABLE2 
---------- ---------- ---------- ---------- 
data1  data2  data3  data4  
data1  data2  data5  data6  

这种方法的SQL Fiddle。

---

您可以使用XQuery and XMLTable代替旧的提取物语法：

select info1, info2, variable1, variable2 
from table_name, 
    xmltable('declare namespace t = "http://url/of/custom/namespace"; (: :) 
    for $i in /t:MsgNameXML 
     let $info1 := $i/t:info1, 
     $info2 := $i/t:info2 
     for $j in $i/t:Section/t:SubSection/t:SubSubSection 
     let $variable1 := $j/t:variable1, 
      $variable2 := $j/t:variable2 
     return <tmp> 
      <info1>{$info1}</info1> 
      <info2>{$info2}</info2> 
      <variable1>{$variable1}</variable1> 
      <variable2>{$variable2}</variable2> 
     </tmp>' 
passing xmltype(CLOB_COLUMN_NAME) 
columns 
    info1  char(10) path '/tmp/info1', 
    info2  char(10) path '/tmp/info2', 
    variable1 char(10) path '/tmp/variable1', 
    variable2 char(10) path '/tmp/variable2' 
); 

INFO1  INFO2  VARIABLE1 VARIABLE2 
---------- ---------- ---------- ---------- 
data1  data2  data3  data4  
data1  data2  data5  data6  

SQL Fiddle。我不得不微调CLOB的值来使其有效;将xsi=更改为xmlns:xsi=并为默认名称空间xmlns:x="http://url/of/default/namespace"设置虚拟名称。我认为这是所有...

XQuery是一个“基于序列的功能语言”，它使用"FLOWR" expressions。具体来说，有两个for循环遍历结构，let表达式将命名空间变量分配给局部变量，然后以更简单的XML结构返回，从中可以通过它们的新路径提取列。 （您似乎无法直接在path字符串中使用名称空间）。

应该相当容易地展开此操作，以便从消息中获取更多值 - 更多let表达式和更多匹配path提取。

但是我真的只是抓住这个东西的表面，所以可能会有更简单/更好/更快的方法来做到这一点;无论如何，希望这是XQuery方法的起点。