Geek Answers Handbook

Oracle SQL extractvalue of multiple items

I spent several days looking for a simple solution to the next problem, and I need help. I have an 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 values ​​in which we have multi-valued elements from our application using an SQL query. The exception of data failures will always correspond to c2 m = "1" and c3 m = "1" if there is c1 m = "1" and so on. The table is too big to hit it several times to extract each element, so I need to pull them out of xmlrecord for one line access. I tried internal joins (1 = 1) and xmltables, but always get NULLS in the returned data or every new match on a new line.Extracting a value from the top level does not work for me in this case due to the xml structure

Our base table data structure:

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

The above entries will work fine, as there are no fields with multiple valuses. Where am I afraid when the data stored in XMLRecord is similar to the following:

<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 output format 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
+4
source share
2 answers

Thanks to everyone for your comments, but I managed to get the solution I needed by creating a connection that works for this instance. The best part about this is that it will work regardless of how many records the seller throws at us. In some cases, the attributes "m" work up to 9 or 10.

(1 = 1) . 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)
+1

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, , .

0

More articles:


All Articles