How do I get complete string from main tag XML in oracle

See more:

Oracle

I have some typical issue to parse XML for the values.

sample xml:

<emp>
<name>John</name>
<id1>123</id1>
<address><street>down town</street></address>
</emp>

i am trying to parse this xml using below query:

SELECT name,id1,address
                     FROM (SELECT XMLTYPE ('<emp>
<name>John</name>
<id1>123</id1>
<address><street>down town</street></address>
</emp>') object_value FROM DUAL) t,
                          XMLTABLE (
                             '/emp'
                             PASSING t.object_value
                             COLUMNS      name varchar2(2000)   PATH '/emp/name',
                             id1 number   PATH '/emp/id1',
                             address clob   PATH '/emp/address');                

its returning output:

name id1 address
John 123 down town

some times address will come string like above and also with xml tags. I would like to return both values like below.

expected output should be:

name id1 address
John 123 <street>down town</street>


How can i get this? Could you please assist?

What I have tried:

<pre>SELECT name,id1,address
                     FROM (SELECT XMLTYPE ('<emp>
<name>John</name>
<id1>123</id1>
<address><street>down town</street></address>
</emp>') object_value FROM DUAL) t,
                          XMLTABLE (
                             '/emp'
                             PASSING t.object_value
                             COLUMNS      name varchar2(2000)   PATH '/emp/name',
                             id1 number   PATH '/emp/id1',
                             address clob   PATH '/emp/address');