Skip to Main Content
  • Questions

  • How to extract XML data using extract function

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, karthick.

Asked: October 17, 2018 - 3:31 pm UTC

Last updated: October 20, 2018 - 4:29 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

inserted row by using below statemet :- 

insert into xmlt values('<?xml version="1.0"?>
<ROWSET> <ROW> <NAME>karthick</NAME> <SALARY>3400</SALARY> </ROW> <ROW> <NAME>c</NAME> <SALARY>1</SALARY> </ROW> <ROW> <NAME>mani</NAME> <SALARY>1</SALARY> </ROW>
 <ROW> <NAME>vicky</NAME> <SALARY>100</SALARY> </ROW> <ROW> <NAME>ram</NAME> <SALARY>1000</SALARY>  </ROW> </ROWSET>');


now trying to extract values from table ,trialxml with xmltype datatype, by using below statement, but it shows null values.

SELECT extract(value(d), '//name/text()').getStringVal() AS name,
       extract(value(d), '//salary/text()').getNumberVal() AS salary
FROM   XMLt x,
       table(xmlsequence(extract(x.trialxml, '/ROWSET/ROW'))) d;


could you pls tell me what i should do to extract data from xml table

and Chris said...

XMLSequence is deprecated as of 11.2.

Looks like a case for XMLTable to me:

create table xmlt (
  trialxml xmltype
);
insert into xmlt values('<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
  <ROW>
    <NAME>karthick</NAME>
    <SALARY>3400</SALARY>
  </ROW>
  <ROW>
    <NAME>c</NAME>
    <SALARY>1</SALARY>
  </ROW>
  <ROW>
    <NAME>mani</NAME>
    <SALARY>1</SALARY>
  </ROW>
  <ROW>
    <NAME>vicky</NAME>
    <SALARY>100</SALARY>
  </ROW>
  <ROW>
    <NAME>ram</NAME>
    <SALARY>1000</SALARY>
  </ROW>
</ROWSET>'
);

SELECT d.*
FROM   XMLt x,
       xmltable (
         '/ROWSET/ROW'
         passing x.trialxml
         columns 
           name varchar2(20) path 'NAME',
           salary number path 'SALARY'
       ) d;

NAME       SALARY   
karthick       3400 
c                 1 
mani              1 
vicky           100 
ram            1000

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

siva, October 18, 2018 - 2:05 pm UTC

what will happen if you run below query in 12c

SELECT extract(value(d), '//name/text()').getStringVal() AS name,
extract(value(d), '//salary/text()').getNumberVal() AS salary
FROM XMLt x,
table(xmlsequence(extract(x.trialxml, '/ROWSET/ROW'))) d;

will it through error or nodata


Connor McDonald
October 20, 2018 - 4:29 pm UTC

That will fail, because 'd' is not the column that comes out of the table() function. By default it is column_value, eg

SQL> create table xmlt ( trivialxml xmltype );

Table created.

SQL>
SQL> insert into xmlt values('<?xml version="1.0"?>
  2  <ROWSET> <ROW> <NAME>karthick</NAME> <SALARY>3400</SALARY> </ROW> <ROW> <NAME>c</NAME> <SALARY>1</SALARY> </ROW> <ROW> <NAME>mani</NAME> <SALARY>1</SALARY> </ROW>
  3   <ROW> <NAME>vicky</NAME> <SALARY>100</SALARY> </ROW> <ROW> <NAME>ram</NAME> <SALARY>1000</SALARY>  </ROW> </ROWSET>');

1 row created.

SQL>
SQL> SELECT column_value
  2  FROM XMLt x,
  3  table(xmlsequence(extract(x.trivialxml, '/ROWSET/ROW'))) d;

COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
<ROW>
  <NAME>karthick</NAME>
  <SALARY>3400</SALARY>
</ROW>

<ROW>
  <NAME>c</NAME>
  <SALARY>1</SALARY>
</ROW>

<ROW>
  <NAME>mani</NAME>
  <SALARY>1</SALARY>
</ROW>

<ROW>
  <NAME>vicky</NAME>
  <SALARY>100</SALARY>
</ROW>

<ROW>
  <NAME>ram</NAME>
  <SALARY>1000</SALARY>
</ROW>


5 rows selected.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.