Oracle extract values from xmltype

Question

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('<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 and save the existing node values to particular local variables. It is been used in the case for multiple sets of data and 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 and 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()
     END
INTO l_new
FROM TABLE(xmlsequence(EXTRACT(l_xml, '/ROWSET/ROW'))) p;

/ Any better way is appreciated.. Thanks

score 5 · Accepted Answer · answered Jul 09 '13 at 10:45

5

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, and l_age would be 2025. Which might not be what you expect.

answered Jul 09 '13 at 10:45

Alex Poole

183,384
11
179
318

Thanks.. I need to use the local variable which is not null after this, to include in a where condition, that is, I need to use a query like "select * from test where name = l_name" if l_name is not null or I need to use "select * from test where age = l_age" if l_age is not null , within a single query. – ajmalmhd04 Jul 09 '13 at 11:39
@ajmalmhd04 - you could do `where (l_name is not null and name = l_name) or (l_age is not null and age = l_age)`, which avoids implicit data type conversion; or change the `end if; if` in the middle of the block above to `elsif` and have both parts assign to the same string variable. – Alex Poole Jul 09 '13 at 11:50
yeah.. The same already done. Cheers :) !! Everything seems okay now. – ajmalmhd04 Jul 09 '13 at 12:18

Oracle extract values from xmltype

1 Answers1

Linked