Script categories

Wednesday, 9 November 2011

Extract data from single XML node


set serveroutput on
declare
l_XML xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>
                          <customertran>
                            <tran>856</tran>
                          </customertran>');
l_Tran varchar2(10);
begin
  select x.tran
  into l_Tran
  from   (select l_XML as m from dual) d
        ,xmltable ('//customertran' passing d.m
                   columns tran varchar2(40) path 'tran') as x;
  dbms_output.put_line(l_Tran);
end;
/

No comments:

Post a Comment