Home » Developer & Programmer » JDeveloper, Java & XML » Help with xmltable (Oracle12g)
Help with xmltable [message #672568] Thu, 18 October 2018 05:36 Go to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Hello,

I have an XML, I need to extract data into rows. E.g.
with dat as (select xmltype('<xmlas>
<CreDtTm>2018-10-11T07:36:49</CreDtTm>
<Orders>
 <Id>985</Id>
 <Item><Amt>15.00</Amt></Item>
 <Item><Amt>63.00</Amt></Item>
</Orders>
<Orders>
 <Id>145</Id>
 <Item><Amt>11.00</Amt></Item>
 <Item><Amt>97.00</Amt></Item>
 <Item><Amt>45.00</Amt></Item>
</Orders>
</xmlas>' ) x from dual )
select xtbl.amt
from dat, xmltable('/xmlas/Orders/Item'
passing dat.x columns
amt varchar2(100) path 'Amt'
) xtbl

Returns one column and 5 rows. I need the result set to be
Amt    ID   CreDtTm
15.00  985  2018-10-11T07:36:49
63.00  985  2018-10-11T07:36:49
11.00  145  2018-10-11T07:36:49
97.00  145  2018-10-11T07:36:49
45.00  145  2018-10-11T07:36:49

XML can have any number of <Order> and the <Order> can have any number of <item>. The number of rows must be equal to the total number of <item>.

Help, please!
Re: Help with xmltable [message #672572 is a reply to message #672568] Thu, 18 October 2018 08:32 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
It seems I have figured that out. The following select gives the desired output:
with dat as (select xmltype('<xmlas>
<CreDtTm>2018-10-11T07:36:49</CreDtTm>
<Orders>
 <Id>985</Id>
 <Item><Amt>15.00</Amt></Item>
 <Item><Amt>63.00</Amt></Item>
</Orders>
<Orders>
 <Id>145</Id>
 <Item><Amt>11.00</Amt></Item>
 <Item><Amt>97.00</Amt></Item>
 <Item><Amt>45.00</Amt></Item>
</Orders>
</xmlas>' ) x from dual )
select amt.amt,  items.id, xtbl.CreDtTm 
from dat, xmltable('/xmlas'
passing dat.x columns
CreDtTm varchar2(100) path 'CreDtTm'
,Orders xmltype path 'Orders'
) xtbl,
xmltable('/Orders' passing xtbl.orders columns items xmltype path '/Item') orders,
xmltable('/Orders' passing orders.items columns id varchar2(100) path 'Id', item xmltype path 'Item' ) items,
xmltable('/Item' passing items.item columns amt varchar2(100) path 'Amt' )amt

Sorry for disturbing you.
Re: Help with xmltable [message #672788 is a reply to message #672572] Fri, 26 October 2018 10:07 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
Your query returns no result with me.

You could try:

with dat as (select xmltype(
'<xmlas>
  <CreDtTm>2018-10-11T07:36:49</CreDtTm>
  <Orders>
   <Id>985</Id>
   <Item><Amt>15.00</Amt></Item>
   <Item><Amt>63.00</Amt></Item>
  </Orders>
  <Orders>
   <Id>145</Id>
   <Item><Amt>11.00</Amt></Item>
   <Item><Amt>97.00</Amt></Item>
   <Item><Amt>45.00</Amt></Item>
  </Orders>
</xmlas>' ) x from dual)
select CreDtTm, Id, Amt 
from dat, 
xmltable('/xmlas' passing dat.x 
  columns
    CreDtTm varchar2(100) path 'CreDtTm'
   ,Orders xmltype path '/xmlas/Orders') xtbl,
xmltable('/Orders' passing xtbl.orders 
  columns 
    items xmltype path '/Orders'
   ,id varchar2(100) path 'Id') orders,
xmltable('/Orders/Item' passing orders.items
  columns amt varchar2(100) path 'Amt' ) amt;

gives


CREDTTM ID AMT
--------------------------------------
2018-10-11T07:36:49 985 15.00
2018-10-11T07:36:49 985 63.00
2018-10-11T07:36:49 145 11.00
2018-10-11T07:36:49 145 97.00
2018-10-11T07:36:49 145 45.00


[Updated on: Fri, 26 October 2018 10:08]

Report message to a moderator

Previous Topic: xml tree structure using Plsql
Next Topic: Configure JAAS for a connection to Oracle Database
Goto Forum:
  


Current Time: Thu Mar 28 12:23:32 CDT 2024