Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Get XML elements from XML with namespace, without pointing the namespace

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
Best Answer
-
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.
Answers
-
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.
-
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.
-
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 !
-
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; /
-
Oh, you replacing namespace before opening the query, right.. Thanks @odie_63