Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
XML and multiple namespaces

Hello,
I have a quick question about XMLType and how to extract values from it... I have one with many namespaces like this one:
WITH T AS ( SELECT ' <project xmlns="http://www.test.com/xid/v2"> <Node>NodeValue</Node> <Content> <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> <Details> <Id type="Local">123456</Id> <Id type="Remote">654321</Id> </Details> </Document:Document> </Content> </project>' x FROM dual ) SELECT ExtractValue(XMLType(x), '/project/Node', 'xmlns="http://www.test.com/xid/v2"'), ExtractValue(XMLType(x), '/project/Content/Document/Details/Id[@type="Local"]', 'xmlns="http://www.test.com/xid/v2"') FROM t;
And when I try to extract Local Id of a document, it always returns null. Any idea why? I'm using Oracle 11.2;
Thank you for your help.
Best Answer
-
Hi,
persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/'
Almost there, you're missing the last step of the XPath expression : 'ns1:Person'
'/ns0:project'passing xmlparse(x.persons)columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]'
Three mistakes here :
1) You're already passing an XMLType from the first XMLTABLE so you must not use XMLParse here (and there's a syntax error anyway)
2) You're passing a collection of 'Person' so why do you try to extract 'ns0:project' ?
3) The PATH expression is wrong, because of (2)
Here's the working query.
Don't hesitate to ask further questions if necessary.
SQL> WITH T AS ( 2 SELECT '<project xmlns="http://www.test.com/xid/v2"> 3 <Node>NodeValue</Node> 4 <Content> 5 <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> 6 <Details> 7 <Id type="Local">123456</Id> 8 <Id type="Remote">654321</Id> 9 <Person> 10 <Id type="Name">Foo</Id> 11 </Person> 12 <Person> 13 <Id type="Name">Test</Id> 14 </Person> 15 <Person> 16 <Id type="Name">Sub</Id> 17 </Person> 18 </Details> 19 </Document:Document> 20 </Content> 21 </project>' x 22 FROM dual 23 ) 24 SELECT x.node, x.local_id, y.pName 25 FROM t 26 , XMLTABLE( 27 XMLNamespaces( 28 'http://www.test.com/xid/v2' as "ns0" 29 , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 30 ) 31 , '/ns0:project' 32 passing xmlparse(document t.x) 33 columns node varchar2(15) path 'ns0:Node' 34 , local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 35 , persons xmltype path 'ns0:Content/ns1:Document/ns1:Details/ns1:Person' 36 ) x 37 , XMLTable( 38 XMLNamespaces( 39 default 'http://other.domain.be/prj/types/prj/file/1' 40 ) 41 , '/Person' 42 passing x.persons 43 columns pName VARCHAR2(10) PATH 'Id[@type="Name"]' 44 ) y 45 ;NODE LOCAL_ID PNAME--------------- ---------- ----------NodeValue 123456 FooNodeValue 123456 TestNodeValue 123456 Sub
Answers
-
First of all, EXTRACTVALUE and other related proprietary functions are all deprecated.
Use standard XQuery-based functions XMLQuery - when you need a single value - or XMLTable when you have to extract more than one value at a time or generate a row source.
XMLTable is more appropriate in this case as it allows you to :
- parse the XML content once
- use the handy XMLNamespaces clause to declare namespace mappings.
Please read this as well, it provides a general methodology to correctly declare and use namespaces :
https://odieweblog.wordpress.com/2016/06/07/xml-namespaces-101/
WITH T AS (
SELECT '<project xmlns="http://www.test.com/xid/v2">
<Node>NodeValue</Node>
<Content>
<Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">
<Details>
<Id type="Local">123456</Id>
<Id type="Remote">654321</Id>
</Details>
</Document:Document>
</Content>
</project>
' x
FROM dual
)
SELECT x.*
FROM t
, XMLTABLE(
XMLNamespaces(
'http://www.test.com/xid/v2' as "ns0"
, 'http://other.domain.be/prj/types/prj/file/1' as "ns1"
)
, '/ns0:project'
passing xmlparse(document t.x)
columns node varchar2(15) path 'ns0:Node'
, local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]'
) x
; -
Thank you Odie. I will also have a look at your blog.
I will come back to you.
-
Hello Odie,
Thank you for your answer. It works quite great. However, I have an additional question (if you don't mind). Imagine I have the following structure (multi nodes Person) and I want to extract the name and have multi records. Do you think it's doable? I tried with many XMLTable but can't find a way Do you have any idea?
WITH T AS ( SELECT '<project xmlns="http://www.test.com/xid/v2"> <Node>NodeValue</Node> <Content> <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> <Details> <Id type="Local">123456</Id> <Id type="Remote">654321</Id> <Person> <Id type="Name">Foo</Id> </Person> <Person> <Id type="Name">Test</Id> </Person> <Person> <Id type="Name">Sub</Id> </Person> </Details> </Document:Document> </Content> </project> ' x FROM dual ) SELECT x.* , y.* FROM t , XMLTABLE( XMLNamespaces( 'http://www.test.com/xid/v2' as "ns0" , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" ) , '/ns0:project' passing xmlparse(document t.x) columns node varchar2(15) path 'ns0:Node' , local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' , persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/' ) x, XMLTAble( XMLNamespaces( 'http://www.test.com/xid/v2' as "ns0" , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" ), '/ns0:project' passing xmlparse(x.persons) columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]' ) y;
-
Hi,
persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/'
Almost there, you're missing the last step of the XPath expression : 'ns1:Person'
'/ns0:project'passing xmlparse(x.persons)columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]'
Three mistakes here :
1) You're already passing an XMLType from the first XMLTABLE so you must not use XMLParse here (and there's a syntax error anyway)
2) You're passing a collection of 'Person' so why do you try to extract 'ns0:project' ?
3) The PATH expression is wrong, because of (2)
Here's the working query.
Don't hesitate to ask further questions if necessary.
SQL> WITH T AS ( 2 SELECT '<project xmlns="http://www.test.com/xid/v2"> 3 <Node>NodeValue</Node> 4 <Content> 5 <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> 6 <Details> 7 <Id type="Local">123456</Id> 8 <Id type="Remote">654321</Id> 9 <Person> 10 <Id type="Name">Foo</Id> 11 </Person> 12 <Person> 13 <Id type="Name">Test</Id> 14 </Person> 15 <Person> 16 <Id type="Name">Sub</Id> 17 </Person> 18 </Details> 19 </Document:Document> 20 </Content> 21 </project>' x 22 FROM dual 23 ) 24 SELECT x.node, x.local_id, y.pName 25 FROM t 26 , XMLTABLE( 27 XMLNamespaces( 28 'http://www.test.com/xid/v2' as "ns0" 29 , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 30 ) 31 , '/ns0:project' 32 passing xmlparse(document t.x) 33 columns node varchar2(15) path 'ns0:Node' 34 , local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 35 , persons xmltype path 'ns0:Content/ns1:Document/ns1:Details/ns1:Person' 36 ) x 37 , XMLTable( 38 XMLNamespaces( 39 default 'http://other.domain.be/prj/types/prj/file/1' 40 ) 41 , '/Person' 42 passing x.persons 43 columns pName VARCHAR2(10) PATH 'Id[@type="Name"]' 44 ) y 45 ;NODE LOCAL_ID PNAME--------------- ---------- ----------NodeValue 123456 FooNodeValue 123456 TestNodeValue 123456 Sub
-
Thank you Odie. You are the best!!!
Thank you!!!
-
Odie, if I may... one last question....
Imagine I have the following (I added two nodes Entity in the Details). Is it possible to extract the values of both at the same time? Something like this:
, entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username',, entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username'
This doesn't return anything but I was curious to know if it was possible.
Thank you,
WITH T AS ( SELECT '<project xmlns="http://www.test.com/xid/v2"> <Node>NodeValue</Node> <Content> <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> <Details> <Id type="Local">123456</Id> <Id type="Remote">654321</Id> <Entity> <person> <username>foo</username> </person> </Entity> <Entity> <type>physical</type> <person> <username>bar</username> </person> </Entity> </Details> </Document:Document> </Content> </project> ' x FROM dual ) SELECT x.* FROM t , XMLTABLE( XMLNamespaces( 'http://www.test.com/xid/v2' as "ns0" , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" ) , '/ns0:project' passing xmlparse(document t.x) columns node varchar2(15) path 'ns0:Node' , local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' , entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username', , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username' ) x ;
-
user13117585 wrote:Imagine I have the following (I added two nodes Entity in the Details). Is it possible to extract the values of both at the same time? Something like this:, entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username', , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username'
Again, multiple issues :
1) person, username and type elements belong to the 'http://other.domain.be/prj/types/prj/file/1' namespace so you're missing the 'ns1' prefix.
2) type is a child of Entity, not person therefore the predicate 'person[type="Physical"]' doesn't target anything.
3) the type is 'physical', not 'Physical'.
However, If you correct (1) you'll get :
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
because 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/ns1:person/ns1:username' targets multiple nodes.
Do you want this?
SQL> WITH T AS ( 2 SELECT '<project xmlns="http://www.test.com/xid/v2"> 3 <Node>NodeValue</Node> 4 <Content> 5 <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1"> 6 <Details> 7 <Id type="Local">123456</Id> 8 <Id type="Remote">654321</Id> 9 <Entity> 10 <person> 11 <username>foo</username> 12 </person> 13 </Entity> 14 <Entity> 15 <type>physical</type> 16 <person> 17 <username>bar</username> 18 </person> 19 </Entity> 20 </Details> 21 </Document:Document> 22 </Content> 23 </project>' x 24 FROM dual 25 ) 26 SELECT x.* 27 FROM t 28 , XMLTABLE( 29 XMLNamespaces( 30 'http://www.test.com/xid/v2' as "ns0" 31 , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 32 ) 33 , '/ns0:project' 34 passing xmlparse(document t.x) 35 columns node varchar2(15) path 'ns0:Node' 36 , local_id number path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 37 , entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity[not(ns1:type)]/ns1:person/ns1:username' 38 , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity[ns1:type="physical"]/ns1:person/ns1:username' 39 ) x 40 ;NODE LOCAL_ID ENTITYNAME ENTITYPHYSICALNAME--------------- ---------- ------------ -------------------NodeValue 123456 foo bar