Home » Developer & Programmer » JDeveloper, Java & XML » XMLTYPE usage in a query (Oracle 11g)
XMLTYPE usage in a query [message #670054] Thu, 31 May 2018 08:45 Go to next message
srinivas.k2005
Messages: 395
Registered: August 2006
Senior Member

Hi, i am trying to get the output with column and value as separate columns, can you help on below

with data as (
select XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<map>
<entry><string>test1</string><string>123</string></entry>
<entry><string>test2</string><string>GB</string></entry>
</map>') val from dual)
select "col"
from data,
xmltable('//entry' passing val
columns
       "col"   varchar2(100)      path '/entry'
 );


Output i need as two columns as below

col    value
test1  123
test2  GB


Re: XMLTYPE usage in a query [message #670059 is a reply to message #670054] Thu, 31 May 2018 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2  select XMLTYPE(
  3  '<?xml version=''1.0'' encoding=''UTF-8''?>
  4  <map>
  5  <entry><string>test1</string><string>123</string></entry>
  6  <entry><string>test2</string><string>GB</string></entry>
  7  </map>') val from dual)
  8  select "col", "value"
  9  from data,
 10       xmltable('//entry' passing val
 11                columns
 12                   "col"   varchar2(20) path '/entry/string[position()=1]',
 13                   "value" varchar2(20) path '/entry/string[position()=2]'
 14                )
 15  /
col                  value
-------------------- --------------------
test1                123
test2                GB
Re: XMLTYPE usage in a query [message #670060 is a reply to message #670059] Thu, 31 May 2018 11:32 Go to previous message
srinivas.k2005
Messages: 395
Registered: August 2006
Senior Member
Thanks Michel
Previous Topic: installing Jdeveloper
Next Topic: Oracle Retail v16 seeded customization - anyone pulled it off?
Goto Forum:
  


Current Time: Sat Oct 19 00:17:17 CDT 2019