| Extract XML value with no local name [message #649841] |
Fri, 08 April 2016 16:32  |
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  |
 |
Barbara Boehmer
Messages: 9103
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 #649844 is a reply to message #649843] |
Fri, 08 April 2016 17:45 |
|
Barbara Boehmer
Messages: 9103
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
