CODEWITHSUNDEEP
sqlxmloracle-database
Mango
Mango

Reputation: 1

Oracle SQL XML extract

I am trying to extract data from XML using oracle SQL but returns null value. This is my XML:

<?xml version="1.0"?>
<fileinfo>
<header>
<system>ABC</system>
<fileID>1</fileID>
</header>
<data>
<Linearcurve>
<fieldID>123</fieldID>
<fieldName>EFG</fieldName>
<Curve>
<curveID>1</curveID>
<xvalue>23</xvalue>
<lastUpdated>
<month>01</month>
<day>01</day>
<year>2001</year>
<hour>01</hour>
<minute>01</minute>
<second>01</second>
</lastUpdated>
<value>
<valueID>1</valueID>
<lowerCurve>
<points>
<point1>0.22</point1>
</points>
</lowerCurve>
</value>
<value>
<valueID>2</valueID>
<lowerCurve>
<points>
<point1>-0.22</point1>
</points>
</lowerCurve>
</value>
</Curve>
</Linearcurve>

I am trying to extract valueID and point1 values but return a blank value Here is my script

WITH XML_CTE as (select xml.* from file1 u, XMLTABLE('//Linearcurve/Curve'
    PASSING XMLTYPE(u.xmlfile)
    COLUMNS
        curveID PATH './../fieldID'
             ,curveID PATH 'curveID'
        ,valueID PATH '//Curve/value/valueID'
        ,point1 PATH '//value/lowerCurve/points/point1' 
    ) xml
)
select * from xml_cte

Ideally the return would include valueID 1 point1 0.22 valueID 2 poing1 -0.22

Upvotes: 0

Views: 45

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can do this with:

select x.*
from file1 f1
cross join xmltable (
  '//Linearcurve/Curve/value'
  passing xmltype(f1.xmlfile)
  columns
    fieldID PATH './../../fieldID'
   ,curveID PATH './../curveID'
   ,valueID PATH 'valueID'
   ,point1 PATH 'lowerCurve/points/point1' 
) x;

FIELDID | CURVEID | VALUEID | POINT1
:------ | :------ | :------ | :-----
123     | 1       | 1       | 0.22  
123     | 1       | 2       | -0.22

db<>fiddle

Upvotes: 1

Related Questions