Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have an XML that looks like below,

<email_details>
        <VENDOR_ID>73</VENDOR_ID>
        <RGP_NUMBER>1234</RGP_NUMBER>
        .....
        ..... (any number of Child Elements)
    </email_details>

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;

and

SELECT *
    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 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 at 12:35

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.