Oracle extract values from xmltype

This is the code I am currently using:

SET serveroutput ONCREATE OR REPLACEPROCEDURE test_proc(i_xml varchar2)ISl_name VARCHAR2(20);l_age NUMBER;l_xml xmltype;BEGINl_xml := xmltype(i_xml);FOR x IN(SELECT VALUE(p) col_valFROM 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;/BEGINtest_proc('<ROWSET><ROW><name>aa</name><age>20</age></ROW><ROW><name>bbb</name><age>25</age></ROW></ROWSET>');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:

<ROWSET><ROW>    <name>aa</name></ROW></ROWSET>

or

<ROWSET><ROW>    <age>18</age></ROW></ROWSET>

/
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()     ENDINTO l_newFROM 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.