Skip to Main Content

SQL & PL/SQL

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

Dominik JasekJun 23 2022 — edited Jun 23 2022

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

select * from 
xmltable(XMLNAMESPACES(default 'urn:easeegas.eu:edigas:nominationandmatching:deliveryorderdocument:5:1'), '/'
  passing xmltype('<?xml version="1.0" encoding="utf-8"?>
<DeliveryOrder_Document release="3" xmlns="urn:easeegas.eu:edigas:nominationandmatching:deliveryorderdocument:5:1">
	<identification>DOC0000001</identification>
	<version>1</version>
	<type>11A</type>
	<creationDateTime>2022-02-22T23:05:02Z</creationDateTime>
	<validityPeriod>2022-02-22T05:00Z/2022-02-23T05:00Z</validityPeriod>
	<contractReference>XXX</contractReference>
	<issuer_MarketParticipant.identification codingScheme="111">00XX000000001</issuer_MarketParticipant.identification>
	<issuer_MarketParticipant.marketRole.code>AAA</issuer_MarketParticipant.marketRole.code>
	<recipient_MarketParticipant.identification codingScheme="111">00XX000000001</recipient_MarketParticipant.identification>
	<recipient_MarketParticipant.marketRole.code>AAA</recipient_MarketParticipant.marketRole.code>
	<ConnectionPoint>
		<identification codingScheme="111">00XX000000001</identification>
		<measureUnit.code>KW1</measureUnit.code>
		<Account>
			<internalAccount codingScheme="AAA">X0001</internalAccount>
			<externalAccount codingScheme="AAA">Y0001</externalAccount>
			<InformationOrigin_TimeSeries>
				<type>AAB</type>
				<Period>
					<timeInterval>2022-02-22T05:00Z/2022-02-23T05:00Z</timeInterval>
					<direction.code>AA1</direction.code>
					<total_Quantity.amount>50000</total_Quantity.amount>
				</Period>
			</InformationOrigin_TimeSeries>
		</Account>
	</ConnectionPoint>
</DeliveryOrder_Document>')
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)

XMLNAMESPACES(default 'urn:easeegas.eu:edigas:nominationandmatching:deliveryorderdocument:5:1')

The query also won't work if you change this

'urn:easeegas.eu:edigas:nominationandmatching:deliveryorderdocument:5:1'

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
https://stackoverflow.com/questions/30509676/oracle-extractvalue-with-dynamic-namespace

This post has been answered by odie_63 on Jun 23 2022
Jump to Answer
Comments
Post Details
Added on Jun 23 2022
5 comments
1,192 views