I’m trying to convert an xml (stored into a CLOB) into a table…
I will love to go from:
<code><formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>
</code>
<code><formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>
</code>
<formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>
to:
enter image description here
(it’s not how i will have created the xml… but it’s the format that i have to work with.)
After a lot of googling, i’m able to extract the first record with the code below, but i will like to have a dynamic list (the number of records in the xml file vary)
<code>SELECT d.profile_name, xtab.Equation
FROM (
select distinct profile_name,
XMLTYPE(up.my_clob) as xml
from mytable
where profile_name like 'A'
) d,
xmltable('/'
PASSING d.xml
COLUMNS Equation varchar2(200) path '//equation/string[1]/text()'
)xtab;
</code>
<code>SELECT d.profile_name, xtab.Equation
FROM (
select distinct profile_name,
XMLTYPE(up.my_clob) as xml
from mytable
where profile_name like 'A'
) d,
xmltable('/'
PASSING d.xml
COLUMNS Equation varchar2(200) path '//equation/string[1]/text()'
)xtab;
</code>
SELECT d.profile_name, xtab.Equation
FROM (
select distinct profile_name,
XMLTYPE(up.my_clob) as xml
from mytable
where profile_name like 'A'
) d,
xmltable('/'
PASSING d.xml
COLUMNS Equation varchar2(200) path '//equation/string[1]/text()'
)xtab;
1
<code>with data(xml) as (
select xmltype(q'~<formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>~')
from dual
)
select ntab.name, etab.equation
from data d,
xmltable(
'/formula/name/string'
PASSING d.xml
COLUMNS
nord for ordinality,
name varchar2(2000) path '.'
) ntab,
xmltable(
'/formula/equation/string'
PASSING d.xml
COLUMNS
eord for ordinality,
Equation varchar2(2000) path '.'
) etab
where nord = eord
;
</code>
<code>with data(xml) as (
select xmltype(q'~<formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>~')
from dual
)
select ntab.name, etab.equation
from data d,
xmltable(
'/formula/name/string'
PASSING d.xml
COLUMNS
nord for ordinality,
name varchar2(2000) path '.'
) ntab,
xmltable(
'/formula/equation/string'
PASSING d.xml
COLUMNS
eord for ordinality,
Equation varchar2(2000) path '.'
) etab
where nord = eord
;
</code>
with data(xml) as (
select xmltype(q'~<formula>
<name>
<string>First</string>
<string>Second</string>
<string>Third</string>
<string>Forth</string>
</name>
<equation>
<string>a+1=b</string>
<string>b*2=c</string>
<string>c+5=d</string>
<string>priority(a,b,c)</string>
</equation>
</formula>~')
from dual
)
select ntab.name, etab.equation
from data d,
xmltable(
'/formula/name/string'
PASSING d.xml
COLUMNS
nord for ordinality,
name varchar2(2000) path '.'
) ntab,
xmltable(
'/formula/equation/string'
PASSING d.xml
COLUMNS
eord for ordinality,
Equation varchar2(2000) path '.'
) etab
where nord = eord
;
And possible to do it with only 1 XMLTABLE:
<code>select
name, equation
from data d,
xmltable(
'for $i at $idx in /formula/equation/string/text()
return <pair><name>{/formula/name/string[$idx]}</name>
<equation>{$i}</equation></pair>'
PASSING d.xml
columns
name varchar2(4000) path './name',
equation varchar2(4000) path './equation'
) t
;
</code>
<code>select
name, equation
from data d,
xmltable(
'for $i at $idx in /formula/equation/string/text()
return <pair><name>{/formula/name/string[$idx]}</name>
<equation>{$i}</equation></pair>'
PASSING d.xml
columns
name varchar2(4000) path './name',
equation varchar2(4000) path './equation'
) t
;
</code>
select
name, equation
from data d,
xmltable(
'for $i at $idx in /formula/equation/string/text()
return <pair><name>{/formula/name/string[$idx]}</name>
<equation>{$i}</equation></pair>'
PASSING d.xml
columns
name varchar2(4000) path './name',
equation varchar2(4000) path './equation'
) t
;
2