Home » Developer & Programmer » JDeveloper, Java & XML » Extract XML value with no local name (Oracle 11g, win 7 service pack-1)
Extract XML value with no local name [message #649841] Fri, 08 April 2016 16:32 Go to next message
vikram_2050
Messages: 10
Registered: June 2005
Location: bangalore
Junior Member

Hi,

I am not able to retrieve any rows from sql query extracting data from xml. Which doesn't have a local name.

If I place a local name. i am able to retrive the data.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
OS Win 7 service pack-1

Below is the xml.

'<asOfParams xmlns="http://abc.com">
   <asOfOffsetTime>0</asOfOffsetTime>
</asOfParams>
'


SQL Query
---------

select * from temp
select x.*,y.* from
varma x,
xmltable(  '/asOfParams'
 passing x.a
 columns
      
         ver_offsetTime number path 'asOfOffsetTime' 
         
         ) y



Thanks in advance
Re: Extract XML value with no local name [message #649842 is a reply to message #649841] Fri, 08 April 2016 17:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
I am not sure what you mean by "local name" or what the question is. I am guessing that by "local name" you mean the xml namespace. I don't know if you are asking how to get the rows or why you have to use an xml namespace to get them. If you are asking how, then please see the demonstration below. If you are asking why you have to use the xml namespace, I can only tell you that, if the xml namespace is in the xml, then you need to use it in any query that references it.

I gather that you have something like this:
SCOTT@orcl> create table varma (a  xmltype)
  2  /

Table created.

SCOTT@orcl> insert into varma (a) values
  2  (xmltype('<asOfParams xmlns="http://abc.com">
  3  	<asOfOffsetTime>0</asOfOffsetTime>
  4  </asOfParams>'))
  5  /

1 row created.

SCOTT@orcl> select * from varma
  2  /

A
--------------------------------------------------------------------------------
<asOfParams xmlns="http://abc.com">
  <asOfOffsetTime>0</asOfOffsetTime>
</asOfParams>


1 row selected.


Apparently, you are trying to do something like this, which does not return any rows:
SCOTT@orcl> select x.*, y.*
  2  from   varma x,
  3  	    xmltable
  4  	      ('/asOfParams'
  5  	       passing x.a
  6  	       columns
  7  		 ver_offsetTime number path 'asOfOffsetTime') y
  8  /

no rows selected


You need to specify the xml namespace in the query, as below.
SCOTT@orcl> select x.*, y.*
  2  from   varma x,
  3  	    xmltable
  4  	      (xmlnamespaces (default 'http://abc.com'),
  5  	       '/asOfParams'
  6  	       passing x.a
  7  	       columns
  8  		 ver_offsetTime number path 'asOfOffsetTime') y
  9  /

A
--------------------------------------------------------------------------------
VER_OFFSETTIME
--------------
<asOfParams xmlns="http://abc.com">
  <asOfOffsetTime>0</asOfOffsetTime>
</asOfParams>
             0


1 row selected.

[Updated on: Fri, 08 April 2016 17:21]

Report message to a moderator

Re: Extract XML value with no local name [message #649843 is a reply to message #649842] Fri, 08 April 2016 17:44 Go to previous messageGo to next message
vikram_2050
Messages: 10
Registered: June 2005
Location: bangalore
Junior Member
Thanks Barbara Boehmer for your response.

I mean to say(local name) with giving the namespace. But in your example you have mentioned that we need to use default which worked.

Once again thanks for your reply.
Re: Extract XML value with no local name [message #649844 is a reply to message #649843] Fri, 08 April 2016 17:45 Go to previous message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
Here is an alternate syntax, that you could also use.

SCOTT@orcl> select x.*, y.*
  2  from   varma x,
  3  	    xmltable
  4  	      (xmlnamespaces ('http://abc.com' as "doc"),
  5  	       '$d/doc:asOfParams'
  6  	       passing x.a as "d"
  7  	       columns
  8  		 ver_offsetTime number path 'doc:asOfOffsetTime') y
  9  /

A
--------------------------------------------------------------------------------
VER_OFFSETTIME
--------------
<asOfParams xmlns="http://abc.com">
  <asOfOffsetTime>0</asOfOffsetTime>
</asOfParams>
             0


1 row selected.

Previous Topic: XML structure into regular table
Next Topic: Stitching strings into xml (merged)
Goto Forum:
  


Current Time: Sun Dec 17 18:47:57 CST 2017

Total time taken to generate the page: 0.01402 seconds