| Help with xmltable [message #672568] |
Thu, 18 October 2018 05:36  |
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  |
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 |
_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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
