Oracle SQL从多个元素中提取值

Question

我花了几天时间寻找以下问题的简单解决方案，我需要一些帮助。我有一个Oracle表，它有两列，recid(帐号)作为主键，xmlrecord存储所有xml数据。我正在尝试使用SQL查询为应用程序导出具有多值项的值。排除数据损坏，如果有c1 m="1"，则总是会有相应的c2 m="2"和c3 m="3"，依此类推。表太大，无法多次命中它来提取每个项，所以我需要在一次访问该行时将它们全部从xmlrecord中取出。我尝试过内部联接(1=1)和xmltables，但最终总是在返回的数据中使用NULLS，或者在新行中使用每个新匹配。在这种情况下，由于xml 的结构，从顶层提取值对我来说不起作用

我们的基表数据结构：

RECID             XMLRECORD
-----------------------------------
0000001           <row><c1>test</c1><c2>test2</c2>....</row>
0000002           <row><c1>test</c1><c2>test2</c2>....</row>

上述记录可以正常工作，因为没有多值字段。当存储在XMLRecord中的数据如下所示时，我会遇到困难：

<row>
<c1>test1</c1>
<c1 m=1>test1_2</c1>
<c2>test2</c2>
<c2 m=1>test2_2</c2>
<c3>test3</c3>
<c3 m=1>test3_2</c3>
</row>

我想要的输出格式如下：

RECID       Col1     Col2     Col3
-----------------------------------
0000003     test1    test2    test3
0000003     test1_2  test2_2  test3_2
0000004     test1    test2    test3
0000004     test1_2  test2_2  test3_2   

Answer 1

感谢大家的评论，但我已经通过构建一个适用于此实例的联接获得了所需的解决方案。它的好处是，无论供应商向我们抛出多少记录，它都能工作。在某些情况下，"m"属性最多可达9或10。

我在(1=1)上使用了一个常见的内部联接，并基于动态ID构建了后续的联接。第一行的ID_NUM结果为"c"，下一行为"c2"，依此类推

SELECT 
t.recid
,t2.VALUE1 
,t3.VALUE2 
,t4.VALUE3 
FROM t
INNER JOIN XMLTABLE('/row/c1'
PASSING t.xmlrecord
ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
VALUE1 VARCHAR(20) path '.') t2
ON (1=1)
INNER JOIN XMLTABLE('/row/c2'
PASSING t.xmlrecord
ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
VALUE2 VARCHAR(20) path '.') t3
ON (t2.ID_NUM=t3.ID_NUM)
INNER JOIN XMLTABLE('/row/c3'
PASSING t.xmlrecord
ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
VALUE3 VARCHAR(20) path '.') t4
ON (t2.ID_NUM=t4.ID_NUM)

Answer 2

您应该能够将EXTRACTVALUE()与XPATH查询一起使用，该查询根据属性选择元素，就像这样。

SELECT RECID
, EXTRACTVALUE(XMLRECORD, '/row/c1[@m=''1'']')
, EXTRACTVALUE(XMLRECORD, '/row/c2[@m=''1'']')
, EXTRACTVALUE(XMLRECORD, '/row/c3[@m=''1'']')
FROM T

然后你可以将所有这些结果与合并

SELECT RECID
, EXTRACTVALUE(XMLRECORD, '/row/c1[not(@m)]')
, EXTRACTVALUE(XMLRECORD, '/row/c2[not(@m)]')
, EXTRACTVALUE(XMLRECORD, '/row/c3[not(@m)]')
FROM T

对于具有多个属性的可能行数，可以继续UNIONS。

我认为在对表进行一次完整扫描时要做到这一点并不容易，因为您正试图为您选择的每一行生成多行。

这是一个很好的例子，说明了为什么在关系数据库中存储XML是一个非常糟糕的主意。

我正试图用XMLTABLE想出一种方法来实现这一点，如果我想办法的话，我会更新答案。