Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
2,727 views