Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an XML that looks like below,

        ..... (any number of Child Elements)

The '...' means that there can be other child nodes inside in future.

I have a requirement in which I have to loop through and extract the "node names" and value from all these child nodes in Oracle 10G.

In 11G,

Select xmltype('<ROOT><A><B>2</B><C>3</C><D>4</D></A></ROOT>').extract('ROOT/A/*[2]').getrootelement() From dual;


    FROM XMLTable('ROOT/A/*'
           passing xmltype('<ROOT><A><B>2</B><C>3</C><D>4</D></A></ROOT>')
           columns node_name  varchar2(30) path 'name(.)'
                 , node_value varchar2(30) path '.'

gives me the solution but it is not working in 10G. Please help.

share|improve this question
When you say it's not working in 10G, please be explicit. What happens when you run it in 10G in comparison to 11G? – Andrew Brennan Apr 11 '14 at 11:46
When I run Select xmltype('<ROOT><A><B>2</B><C>3</C><D>4</D></A></ROOT>').extract('ROOT/A/*[2]').g‌​etrootelement() From dual; in 11g I get the output as "C" that is the name of the 2nd node under node A. But when I run the same code in 10G, it returns null. Same for the other sample select statement. – Subhaprasad Mukherjee Apr 11 '14 at 12:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.