Select xml element value in Oracle
13,266
Solution 1
Since the a
element does not have the namespace, you can first extract its child elements without using namespaces in the function, and then extract the value from the b
with the namespace:
Try:
select extract(extract(myColumn, 'a/*'),
'b/c/text()',
'xmlns=urn:www.someSite.com/myModel')
from myTable
Solution 2
select a.*
from XMLTABLE(
XMLNAMESPACES('urn:www.someSite.com/myModel' AS "ns"),
'/*'
PASSING my.myColumn
COLUMNS
val VARCHAR2(2000) PATH '/a/ns:b/ns:c'
) a, myTable my;
Author by
axl g
Updated on June 13, 2022Comments
-
axl g about 2 years
I am trying to extract a value from an xml element, located in an XMLTYPE column in an Oracle Table. The xml element which I am trying to extract have a parent for which a namespace is defined. The xml looks something like:
<a> <b xmlns="urn:www.someSite.com/myModel"> <c>my value</c> </b> </a>
If I want to extract the content of the "a" element, its context is correctly returned:
SELECT Extract(myColumn, '/a') FROM myTable;
But for returning the content of the "c" element I didn't succeed to find any version to work. The following instructions does not work:
SELECT Extract(myColumn, '/a/b/c') FROM myTable; SELECT Extract(myColumn, '/a/b/c', 'xmlns="urn:www.someSite.com/myModel"') FROM myTable; SELECT Extract(myColumn, '/a/b/c', 'urn:www.someSite.com/myModel') FROM myTable;
Can anybody help me, with the extract statement that would work in this case?
-
REW about 9 yearsI will add that this is also the better modern solution now since extract/extractvalue are depreciated.