1

I have a query that extract me some the XML nodes from my clob.

select pid, name, xml from (
select d.pid, d.name
, EXTRACT(xmltype(d.data), '//ns1:myId', 'xmlns:ns1="http://acme.com/') xml
from DATA d
order by d.pid desc
)
;

But I'd like to see what the parent elements name of the extracted xmlnode actually is. I tried

, EXTRACT(xmltype(d.data), '//ns1:myId/../name()', ...) xml

and

, EXTRACT(xmltype(d.data), '//ns1:myId/name()', ...) xml

and

, EXTRACT(xmltype(d.data), '//ns1:myId/local-name()', ...) xml

but Oracle rejects all f them with "invalid token" error messages.

My Oracle Version is "11.2.0.3.0".

towi
  • 21,587
  • 28
  • 106
  • 187

1 Answers1

2

Extract lets you look higher up the path, but (as noted in MOS document 301709.1; which is for 9i but still seems valid, apart from the error shown):

Using the XPATH function name() to return the element name is not possible, because the methods extract() and extractValue() currently only support XPATH operations returning a node-set.

So you can't use the name(), local-name() etc. functions, on the current or parent node. There is a workaround of sorts in that document, which can be simplified slightly rom their example to:

EXTRACT(xmltype(d.data), '//ns1:myId/..', 
  'xmlns:ns1="http://acme.com/').getRootElement() xml2

Or in a slightly different form:

xmltype(d.data).extract('//ns1:myId/..', 
  'xmlns:ns1="http://acme.com/').getRootElement()

Demo with both:

with data (pid, name, data) as (
  select 42, 'Test', '<?xml version="1.0" encoding="ISO-8859-1"?>
  <ns1:root xmlns:ns1="http://acme.com/"><ns1:parent><ns1:myId>1234</ns1:myId></ns1:parent></ns1:root>' from dual
)
select d.pid, d.name
, EXTRACT(xmltype(d.data), '//ns1:myId', 'xmlns:ns1="http://acme.com/') xml
, EXTRACT(xmltype(d.data), '//ns1:myId/..', 'xmlns:ns1="http://acme.com/').getRootElement() xml2
, xmltype(d.data).extract('//ns1:myId/..', 'xmlns:ns1="http://acme.com/').getRootElement() xml3
from DATA d
order by d.pid desc
;

       PID NAME XML                            XML2       XML3     
---------- ---- ------------------------------ ---------- ----------
        42 Test <ns1:myId xmlns:ns1="http://ac parent     parent    
                me.com/">1234</ns1:myId>

But extract() is deprecated anyway. You could do this with XMLTable:

with data (pid, name, data) as (
  select 42, 'Test', '<?xml version="1.0" encoding="ISO-8859-1"?>
  <ns1:root xmlns:ns1="http://acme.com/"><parent><myId>1234</myId></parent></ns1:root>' from dual
)
select d.pid, d.name, x.*
from data d
cross join xmltable(xmlnamespaces('http://acme.com/' as "ns1"),
  '/ns1:*//myId'
  passing xmltype(d.data)
  columns myId number path '.',
    parent varchar2(20) path './../local-name()'
) x
order by d.pid desc;

       PID NAME       MYID PARENT             
---------- ---- ---------- --------------------
        42 Test       1234 parent

If you need something more complicated you can pull whatever you need from the node levels, as shown in this answer; but from what you've said that's overkill here.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Of course, the result of `extract` can not be traversed upwards. But I thought I could modify the XPath expression, so that `extract` returns the parent node for me to inspect. – towi Nov 10 '15 at 09:46
  • @towi - i meant within the XPath rather than post-extract, but that was incorrect anyway; it's the functions that aren't supported. I've updated the answer to show how it can be done. I'd still use the newer functionality over the deprecated `extract()` though. – Alex Poole Nov 10 '15 at 11:02
  • 1
    That worked. Thanks for the additional information that `extract` is deprecated. I don't understand your non-deprecated solution, yet, but I will, when I have to. There are too many things in it I have only seen in "passing" and they always make my eyes "cross". – towi Nov 11 '15 at 10:30