我花了几天时间寻找以下问题的简单解决方案,我需要一些帮助.我有一个带有两列的Oracle表,recid(帐号)作为主键,xmlrecord存储所有xml数据.我试图使用SQL查询为我们的应用程序导出具有多值项的值.排除数据损坏,如果存在c1 m =“1”,则总会有相应的c2 m =“1”和c3 m =“1”,依此类推.该表太大而无法多次触发它以提取每个项目,因此我需要在行的一次访问中将它们全部拉出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>
由于没有多个valuse字段,上述记录可以正常工作.我正在努力的地方是存储在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
解决方法
谢谢你的意见,但我已经设法通过构建一个适用于此实例的连接来获得我需要的解决方案.关于它的好处是,无论供应商向我们投放多少记录,它都会起作用.在某些情况下,“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(.),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(.),VALUE3 VARCHAR(20) path '.') t4
ON (t2.ID_NUM=t4.ID_NUM)
