Geek Answers Handbook

Retrieve first occurring value from XML using oracle parsing

I am not an Oracle expert, but as required, I use Oracle Parser for Parsing Xml. For xml ie listed below

<?xml version="1.0" encoding="iso-8859-1" ?> <SearchOutput> <rowArray> <Row> <cellArray> <Cell> <columnId>1</columnId> <valueArray> <Value> <value>IR000024575453</value> </Value> </valueArray> </Cell> <Cell> <columnId>5</columnId> <valueArray> <Value> <value>AZ12604823-001</value> </Value> </valueArray> </Cell> <Cell> <columnId>2</columnId> <valueArray> <Value> <value>IT06686</value> </Value> </valueArray> </Cell> <Cell> <columnId>9</columnId> <valueArray> <Value> <value>Hu Mics Metab K</value> </Value> </valueArray> </Cell> <Cell> <columnId>8</columnId> <valueArray> <Value> <value>2006-06-21</value> </Value> </valueArray> </Cell> <Cell> <columnId>7</columnId> <valueArray> <Value> <value>2006-07-27</value> </Value> </valueArray> </Cell> </cellArray> </Row> </rowArray> </SearchOutput>

I used this method where l_xmlclob has a CLOB data type and is assigned the xml above.

 FOR r IN ( SELECT rownum rn, cells FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob) columns CELLS XMLTYPE PATH './cellArray') ) LOOP DBMS_OUTPUT.PUT_LINE('Row: '||r.rn); FOR c IN ( SELECT colid, colval FROM xmltable('/cellArray/Cell' passing r.cells columns COLID NUMBER PATH './columnId', COLVAL VARCHAR(20) PATH './valueArray/Value/value') ) LOOP DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval); END LOOP; END LOOP;

and his working tone and his output is similar to

 Row: 1 colid, col value: 1, IR000024575453 colid, col value: 5, AZ12604823-001 colid, col value: 2, IT06686 colid, col value: 9, Hu Mics Metab K colid, col value: 8, 2006-06-21 colid, col value: 7, 2006-07-27

but the problem arises if there are two values ​​one after another in xml and I want to select the first occurrence only, i.e. for the lower xml

 <?xml version="1.0" encoding="iso-8859-1" ?> <SearchOutput> <rowArray> <Row> <cellArray> <Cell> <columnId>1</columnId> <valueArray> <Value> <value>Uganda</value> </Value> <Value> <value>Italy</value> </Value> </valueArray> </Cell> <Cell> <columnId>5</columnId> <valueArray> <Value> <value>AZ12604823-001</value> </Value> </valueArray> </Cell> <Cell> <columnId>2</columnId> <valueArray> <Value> <value>IT06686</value> </Value> </valueArray> </Cell> <Cell> <columnId>9</columnId> <valueArray> <Value> <value>Hu Mics Metab K</value> </Value> </valueArray> </Cell> <Cell> <columnId>8</columnId> <valueArray> <Value> <value>2006-06-21</value> </Value> </valueArray> </Cell> <Cell> <columnId>7</columnId> <valueArray> <Value> <value>2006-07-27</value> </Value> <Value> <value>2012-02-27</value> </Value> </valueArray> </Cell> </cellArray> </Row> </rowArray> </SearchOutput>

I want "uganda, AZ12604823-001, IT06686, Hu Mics Metab K, 2006-06-21,2006-07-27" which will be selected only within this value Array Not "Italy and 2012-02-27". But I don’t know how to modify the existing code.

+4
source share
1 answer

I think this is what you are looking for -

 FOR r IN ( SELECT rownum rn, cells FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob) columns CELLS XMLTYPE PATH './cellArray') f ) LOOP DBMS_OUTPUT.PUT_LINE('Row: '||r.rn); FOR c IN ( SELECT colid, colval FROM xmltable('for $i in /cellArray/Cell return $i' passing r.cells columns COLID NUMBER PATH 'columnId', COLVAL VARCHAR(20) PATH 'valueArray/Value[1]/value') ) LOOP DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval); END LOOP; END LOOP; end;

OR

 FOR r IN ( SELECT rownum rn, cells FROM xmltable('/SearchOutput/rowArray/Row' passing XMLTYPE(l_xmlclob) columns CELLS XMLTYPE PATH './cellArray') ) LOOP DBMS_OUTPUT.PUT_LINE('Row: '||r.rn); FOR c IN ( SELECT colid, colval FROM xmltable('/cellArray/Cell' passing r.cells columns COLID NUMBER PATH './columnId', COLVAL VARCHAR(20) PATH './valueArray/Value[1]/value') ) LOOP DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval); END LOOP; END LOOP;

Output -

 Row: 1 colid, col value: 1, Uganda colid, col value: 5, AZ12604823-001 colid, col value: 2, IT06686 colid, col value: 9, Hu Mics Metab K colid, col value: 8, 2006-06-21 colid, col value: 7, 2006-07-27

Value[1] will provide you with the first of the multi-line elements, that is, the first Value singleton.

+1
source

More articles:


All Articles