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

Dominik Jasek

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

Spoiler Warning

https://stackoverflow.com/questions/30509676/oracle-extractvalue-with-dynamic-namespace

odie_63

There are different ways to approach the problem.

• Where's the input XML located? Local PL/SQL variable, Table column ?
• Which data type? XMLType, CLOB, ... ?

Personally, for performance reason, I'd make the whole query dynamic with a substitutable placeholder for the default namespace, and of course a bind variable for the input XML (if stored in a variable). That way the optimizer would still know at parse-time all access paths necessary to resolve the XQuery expressions.

That being said, Oracle's XQuery implementation supports wildcards in lieu of namespace prefixes, so you could do this, and remove XMLNAMESPACES clause :

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'

Be aware, it'll be slower on large documents, but maybe it's not a concern.

odie_63

There are different ways to approach the problem.

• Where's the input XML located? Local PL/SQL variable, Table column ?
• Which data type? XMLType, CLOB, ... ?

Personally, for performance reason, I'd make the whole query dynamic with a substitutable placeholder for the default namespace, and of course a bind variable for the input XML (if stored in a variable). That way the optimizer would still know at parse-time all access paths necessary to resolve the XQuery expressions.

That being said, Oracle's XQuery implementation supports wildcards in lieu of namespace prefixes, so you could do this, and remove XMLNAMESPACES clause :

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'

Be aware, it'll be slower on large documents, but maybe it's not a concern.

padders

I'm not 100% clear what you mean by 'not editing the XML' but it seems there could also be an option to transform the XMLs (e.g. with XSL/xquery) beforehand or in-line to conform or remove the relevant namespace, with the obvious caveat that you would need to investigate the impact on the integrity of naming within the XML.

Dominik Jasek

Thanks guys for the answers.

@odie_63 - actually all combination you mention are in place. I have it as a CLOB and as XMLTYPE in a table. I have it also as a parameter (both CLOB and XMLTYPE too). Any solution which may resolve that without using depreciated method are ok for me.. and actually your solution with additional"*:" wildecard do the work ! Thank you !

Please bear in mind that the XML structure (including namespaces) is generated from XSD which handle by external organization and I don't have possibility to edit it.

For my curiosity, how the query should looks like to support your way ?

Personally, for performance reason, I'd make the whole query dynamic with a substitutable placeholder for the default namespace, and of course a bind variable for the input XML (if stored in a variable). That way the optimizer would still know at parse-time all access paths necessary to resolve the XQuery expressions.

@padders - that's what I'm actually doing right now :) but instead XLS I'm using regexp_replace on a CLOB. Do the trcik, but I don't like it. There are area for bugs, which I would like to avid.

Have a nice weekend !

odie_63

Here's a sample XSL stylesheet that takes all elements out of any namespaces, and leave out comments and PIs as well. It should do the job in your case, again assuming there's only one default namespace declaration, which appears in the root element, and applying to all elements in the document :

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" omit-xml-declaration="yes"/>
 <xsl:template match="@*">
  <xsl:copy/>
 </xsl:template>
 <xsl:template match="*">
  <xsl:element name="{local-name()}">
   <xsl:apply-templates select="@*|node()"/>
  </xsl:element>
 </xsl:template>
</xsl:stylesheet>

You can apply it to the input XML document via XMLTransform SQL function, or XMLType.transform method in a PL/SQL context.

For my curiosity, how the query should looks like to support your way ?

This is the idea, but it would be significant only for large documents stored in an XMLType column :

declare

 p_xml  xmltype := xmltype('<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>
</DeliveryOrder_Document>');

 p_ns   varchar2(256) := 'urn:easeegas.eu:edigas:nominationandmatching:deliveryorderdocument:5:1';
 l_query varchar2(32767) := q'~
select * 
from xmltable(
       xmlnamespaces(default '$$DEFAULT_NS')
     , '/' 
       passing :1 
       columns identity  varchar2(200) path '/*/identification'
             , version   number        path '//version'
)
~';

 rc    sys_refcursor;

 type result_t is record (
  identity    varchar2(200)    
 , version    number    
 );
  
 l_result result_t;
  
begin  
  
 l_query := replace(l_query, '$$DEFAULT_NS', p_ns);
 open rc for l_query using p_xml;
 fetch rc into l_result;
 close rc;
  
 dbms_output.put_line('identity='||l_result.identity);
 dbms_output.put_line('version='||l_result.version);

end;
/

Dominik Jasek

Oh, you replacing namespace before opening the query, right.. Thanks @odie_63