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