创建Oracle XML视图

Question

我有一个Oracle表，它将大量数据作为XML存储在CLOB中，例如，它将状态整数映射到自定义的状态名称。以下是一些简化的XML：

<metadata>
    <states>
        <state status="0" label="New" />
        <state status="1" label="Data-Entry" />
        <state status="25" label="Approve-Data" /> 
    </states>
</metadata>

我编写了一个查询，从数据库中提取上述XML并确定状态名，给出的状态整数为25：

select id As METADATA_ID, EXTRACTVALUE(xmltype(XML_STRING), '/metadata/states/state[@status="25"]/@label') from metadata;

结果：342, "Approve-Data"。到现在为止还好。上面的查询为该状态创建了所有不同标签的列表。不过，我想要做的是创建一个包含以下列的视图：

METADATA_ID   |   STATUS_NUM   |    LABEL
342               0                 "New"
342               1                 "Data-Entry"
342               25                "Approve-Data"
674               3                 "Something-Else"
674               6                 "More-Data"

这样，我就可以为不同的元数据ID查找所有不同的映射，并执行普通的Oracle联接。有办法做到这一点，考虑到在身份数字的差距？

Answer 1

好消息大家！我找到了一个使用XMLTABLE的解决方案(请原谅我任意大写的做法)：

select 
  m.id as metadata_id, x.*
FROM
  metadata m,
  XMLTABLE('/metadata/states/state'
       PASSING xmltype(m.xml_string)
       COLUMNS status_id INTEGER PATH '@status',
               status_label VARCHAR(32) PATH '@label') x;

Answer 2

您需要以某种方式将一行乘以它所拥有的状态标记的数量。

第一种解决办法，

对EXTRACTVALUE进行更改，以便它将返回@status/label;@status/label；例如0/New；1/data-Entry；25/审批-数据

选择分隔符/存在问题；这样您的标签中就没有它们。

然后是将字符串转换为表

第二解

创建数字表并将其与您的表连接起来。然后，不要使用state[@status="25"]，而是使用state[i]，其中我将来自于数字表

第三解

按照这个答案中的第一个链接，您可以找到XMLSEQUENCE的示例来提取行