Home » Developer & Programmer » JDeveloper, Java & XML » creating views from XML (Oracle 11g)
creating views from XML [message #635074] Thu, 19 March 2015 14:13 Go to next message
clobber
Messages: 2
Registered: March 2015
Junior Member
Hey guys,

I'm new in this forum Smile but I'm not that new to Oracle. I have a current problem that I need to generate views from a xml document insertet in a clob. I created a function that selected the data of the file in a varchar2. Now the problem is how to get the string to split the data of the xml, like

<book>
<author>Mr. Dunno</author>
<year>2000</year>
<price>19.99</price>
</book>

into a view named 'book', with the columns 'author', 'year' and 'price'.

Can you push me in the right direction? I dont even know where and what to research.

Sorry for my bad English, I'm not native speaking.

Regards Clobber Smile

Re: creating views from XML [message #635079 is a reply to message #635074] Thu, 19 March 2015 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    data as (
  3      select
  4  '<book>
  5  <author>Mr. Dunno</author>
  6  <year>2000</year>
  7  <price>19.99</price>
  8  </book>' val
  9      from dual
 10    )
 11  select cast(extractvalue(value(x), '//author') as varchar2(30)) author,
 12         to_number(extractvalue(value(x), '//year')) year,
 13         to_number(extractvalue(value(x), '//price')) price
 14  from data,
 15       table(xmlsequence(extract(xmltype(val), '//book'))) x
 16  /
AUTHOR                               YEAR      PRICE
------------------------------ ---------- ----------
Mr. Dunno                            2000      19.99

1 row selected.

SQL> with
  2    data as (
  3      select
  4  '<book>
  5  <author>Mr. Dunno</author>
  6  <year>2000</year>
  7  <price>19.99</price>
  8  </book>' val
  9      from dual
 10    )
 11  select x.author, x.year, x.price
 12  from data,
 13       xmltable('/book'
 14                passing xmltype (data.val)
 15                columns
 16                  "AUTHOR" varchar2(30) path '/book/author',
 17                  "YEAR"   number       path '/book/year',
 18                  "PRICE"  number       path '/book/price'
 19       ) x
 20  /
AUTHOR                               YEAR      PRICE
------------------------------ ---------- ----------
Mr. Dunno                            2000      19.99

1 row selected.

Re: creating views from XML [message #635082 is a reply to message #635079] Thu, 19 March 2015 16:10 Go to previous message
clobber
Messages: 2
Registered: March 2015
Junior Member
thank you very much friend Smile I#m going to try it immediately Smile
Previous Topic: Getting Wrong result set from XMLTable funciton
Next Topic: VehicleRecord2:428: cannot find symbol
Goto Forum:
  


Current Time: Sun Jan 26 09:00:03 CST 2020