I have spent days looking for a simple solution to the following problem and I need some help please. I have a Oracle table with two columns, recid (Account Number) as the primary key and xmlrecord which stores all the xml data. I am trying to export the values where we have multi valued items from for our application using a SQL query. Excluding data corruptions there will always be a corresponding c2 m="1" and c3 m="1" if there is a c1 m="1" and so on. The table is too big to hit it multiple times to extract each item so I need to pull them all out of the xmlrecord on one access of the row. I have tried inner joins (1=1) and xmltables but always end up with NULLS in the data returned or each new match on a new line. Extract value from the top level doesn't work for me in this instance due to the structure of the xml
Our data structure of the base table:
RECID XMLRECORD
-----------------------------------
0000001 <row><c1>test</c1><c2>test2</c2>....</row>
0000002 <row><c1>test</c1><c2>test2</c2>....</row>
The above records would work fine as there are no multi valuse fields. Where I'm struggling is when the data stored in XMLRecord is like the below:
<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>
The format of the output I would like is below:
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
Thankyou all for your comments but I have managed to get the solution I need by build a join that works for this instance. The good thing about it, is that it will work no matter how many record's the vendor throws at us. In some instances the "m" attributes run up to 9 or 10.
I used a usual inner join on (1=1) and built the subsequent joins based on a dynamic ID. The result of ID_NUM for the first row is "c" and the next row is "c2" and so on.
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)