Oracle SQL extractvalue from multiple elements

Question

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   

Answer 1

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)

Answer 2

You should be able to use EXTRACTVALUE() with an XPATH query that selects elements based on the attribute, like so.

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

You could then UNION ALL this result with

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

You could continue the UNIONS for the number of possible rows with multiple attributes.

I don't think this is going to be easy to do in one full scan of the table, because you are trying to generate multiple rows for each individual row you select.

This is a great example of why storing XML in a relational database is a pretty bad idea.

I'm trying to come up with a way to do this with XMLTABLE, i'll update the answer if I think of a way.