/ Oracle extract values from xmltype

Oracle extract values from xmltype

This is the code I am currently using:

SET serveroutput ON CREATE OR REPLACE PROCEDURE test_proc(i_xml varchar2) IS l_name VARCHAR2(20); l_age NUMBER; l_xml xmltype; BEGIN l_xml := xmltype(i_xml); FOR x IN (SELECT VALUE(p) col_val FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/ROWSET/ROW'))) p ) LOOP IF x.col_val.existSNode('/ROW/name/text()') > 0 THEN l_name:= x.col_val.EXTRACT('/ROW/name/text()').getstringVal(); END IF; IF x.col_val.existSNode('/ROW/age/text()') > 0 THEN l_age := x.col_val.EXTRACT('/ROW/age/text()').getstringVal(); END IF; end loop; end; / BEGIN test_proc(' aa 20 bbb 25 '); END; /

The above code uses xml to extract & save the existing node values to particular local variables. It is been used in the case for multiple sets of data & is working fine. I just wanted to know whether can I able to use the same without “for x loop”, because I will only have one data in the i_xml from now onwards & I will only have either
name or age tags .

The following code should be used to save into l_name or l_age without the “loop” method like I used above:




And I’ve tried using the following:

SELECT CASE WHEN VALUE(p).existsNode('/ROW/name/text()') = 1 THEN p.EXTRACT('/ROW/name/text()').getstringVal() WHEN VALUE(P).existsNode('/ROW/age/text()') = 1 THEN p.EXTRACT('/ROW/age/text()').getstringVal() END INTO l_new FROM TABLE(xmlsequence(EXTRACT(l_xml, '/ROWSET/ROW'))) p;

Any better way is appreciated.. Thanks

If you’re really sure you’ll only have one `ROW` then you can do:

begin l_xml := xmltype(i_xml); if l_xml.existsnode('/ROWSET/ROW/name') > 0 then l_name := l_xml.extract('/ROWSET/ROW/name/text()').getstringval(); end if; if l_xml.existsnode('/ROWSET/ROW/age') > 0 then l_age := l_xml.extract('/ROWSET/ROW/age/text()').getnumberval(); end if; end;

That will work if you have name or age, or both, or neither (where ‘work’ means doesn’t error, at least). If you did have more than one row it would concatenate the results, so with your original data, l_name would be aabbb, & l_age would be 2025. Which might not be what you expect.