Parsing XML via ORACLE Query using XMLTYPE/XMLTABLE

26 views Asked by At

I Wish to return a section of XML from a Datatable using SQL.

<Layer>
                <LayerName>ALPHA</LayerName>
                <coordinates>
                    <x>-5</x>
                    <y>2</y>
                </coordinates>
                <coordinates>
                    <x>-5</x>
                    <y>1</y>
                </coordinates>
                <coordinates>
                    <x>-5</x>
                    <y>0</y>
                </coordinates>
                <coordinates>
                    <x>-5</x>
                    <y>-1</y>
                </coordinates>
                <coordinates>
                    <x>-5</x>
                    <y>-2</y>
                </coordinates>
                <LayerName>BETA</LayerName>
                <coordinates>
                    <x>-4</x>
                    <y>3</y>
                </coordinates>
                <coordinates>
                    <x>-4</x>
                    <y>2</y>
                </coordinates>
                <LayerName>GAMMA</LayerName>
                <coordinates>
                    <x>-4</x>
                    <y>1</y>
                </coordinates>
                <coordinates>
                    <x>-4</x>
                    <y>0</y>
                </coordinates>
                <coordinates>
                    <x>-4</x>
                    <y>-1</y>
                </coordinates>
            </Layer>

I'm taking the approach of trying to bring the data back as an XML Table but cannot seem to get it to work as expected.

SELECT X.PRODUCT, X2.LayerName, X2.X, X2.Y
FROM( 
        SELECT PRODUCT, XMLTYPE(xml_data) AS xmlData FROM XMLTABLE) X,
      XMLTABLE('/Layer' PASSING x.xmlData columns 
         LayerName varchar2(100) PATH 'LayerName',
         X number PATH 'coordinates/x',
         Y number PATH 'coordinates/y'
      ) X2

Results Table

I tried with various versions of columns and can get EITHER the LayerName or the X,Y but not both

0

There are 0 answers