how to get node name in Oracle SQL extract() with xPath (10g)

sql oracle xpath xmltype nodename

Frank · Oct 19, 2012 · Viewed 10.9k times · Source

Here is a XML file:

<ROOT>
<A>
    <B>2</B>
    <C>3</C>
    <D>4</D>
</A> 
</ROOT>

How to get the tag name "C" through xPath. The function name() does not work here in extract.

It reports Errors:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token

gXmlDOM is the xml string above, how to do this in SQL?

select XMLType(gXmlDOM).extract(p_xmlPath).getStringVal() from dual;

Answer

This might be what you're looking for...

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