Skip to Main Content


Get XML elements from XML with namespace, without pointing the namespace

Dominik JasekJun 23 2022 — edited Jun 23 2022

I have the following code and it's working fine.

select * from 
xmltable(XMLNAMESPACES(default ''), '/'
  passing xmltype('<?xml version="1.0" encoding="utf-8"?>
<DeliveryOrder_Document release="3" xmlns="">
	<issuer_MarketParticipant.identification codingScheme="111">00XX000000001</issuer_MarketParticipant.identification>
	<recipient_MarketParticipant.identification codingScheme="111">00XX000000001</recipient_MarketParticipant.identification>
		<identification codingScheme="111">00XX000000001</identification>
			<internalAccount codingScheme="AAA">X0001</internalAccount>
			<externalAccount codingScheme="AAA">Y0001</externalAccount>
columns identity varchar2(200) path '/*/identification',
      version number path '//version',
      identification varchar2(200) path '/*/issuer_MarketParticipant.identification',
      attrib varchar2(200) path '/*/issuer_MarketParticipant.identification/@codingScheme',
      wrong varchar2(200) path '/*/asdsdad'
      ) t;

But my problem is that I need to create a couple of functions which extract some nodes values from different XMLs with different namespace. What I want to achieve is :
make namespace dynamic OR
use some way to tell Oracle there is NO namespace (without editing XML)
The query doesn't work if I drop this part (I mean it works, but gives you bunch of nulls)


The query also won't work if you change this


to a parameter, as Oracle require that namespace must be a hardcoded string (ORA-19102: XQuery [string] literal expected)
How to do it ? I'm using 19c
The closes answer I found so far was from StackOverflow (check below). But I can't figure it out how to make it work with multiple nodes in single query

This post has been answered by odie_63 on Jun 23 2022
Jump to Answer
Post Details
Added on Jun 23 2022