This discussion is archived
2 Replies Latest reply: Jul 22, 2013 4:56 AM by TimWong765 RSS

Parse XML file and extract data

TimWong765 Newbie
Currently Being Moderated

I'd like to parse an XML file and get some data extracted as columns.

 

Input file country.xml:

<?xml version="1.0" encoding="UTF-8"?>
<MAS Action="Insert">
<Country ObjectId="100000000000000009" VersionId="8"><Id>1</Id><NlTexts><Name Language="de">Land1</Name><Name Language="en">Country1</Name></NlTexts></Country>
<Country ObjectId="100000000000000033" VersionId="2"><Id>2</Id><NlTexts><Name Language="de">Land2</Name><Name Language="en">Country1</Name></NlTexts></Country>
</MAS>

 

I'd like to parse the xmlfile in order to get the following output

Required result:

col1        col2           col3
1            Land1        Country1
2            Land2        Country2

 

or alternatively

 

col1  col2       
1            Land1       
1            Country1
2            Land2       
2            Country2

 

 

I tried extract-function

 

select extract((XMLTYPE(BFILENAME('XML_DAT_DIR', 'country.xml'),
           NLS_CHARSET_ID('AL32UTF8'))) , '/*/*/Id') as "xdata"
from dual;
xdata
------------------------
<Id>1</Id><Id>2</Id>

 

and XMLTABLE (but how can I add the countries now)

 

SELECT *
    FROM XMLTABLE('/*/*/Id'
           PASSING XMLTYPE(BFILENAME('XML_DAT_DIR', 'country.xml'),
           NLS_CHARSET_ID('AL32UTF8'))
       )
  ;
COLUMN_VALUE
------------------------
<Id>1</Id>
<Id>2</Id>

 

 

 

DB version 11.2.0.3 on Windows 64bit

 

Thanks,

Tim

  • 1. Re: Parse XML file and extract data
    odie_63 Guru
    Currently Being Moderated

    Here are a few examples.

     

    For your required output :

    SELECT *

    FROM XMLTable(

           '/MAS/Country'

           passing XMLType(bfilename('TEST_DIR', 'country.xml'), nls_charset_id('AL32UTF8'))

           columns col1 number       path 'Id'

                 , col2 varchar2(30) path 'NlTexts/Name[1]'

                 , col3 varchar2(30) path 'NlTexts/Name[2]'

         )

    ;

     

    or, if the Language attribute is significant :

    SELECT *

    FROM XMLTable(

           '/MAS/Country'

           passing XMLType(bfilename('TEST_DIR', 'country.xml'), nls_charset_id('AL32UTF8'))

           columns col1 number       path 'Id'

                 , col2 varchar2(30) path 'NlTexts/Name[@Language="de"]'

                 , col3 varchar2(30) path 'NlTexts/Name[@Language="en"]'

         )

    ;

     

    For your alternate output :

    SELECT x1.col1

         , x2.col2

         --, x2.col3

    FROM XMLTable(

           '/MAS/Country'

           passing XMLType(bfilename('TEST_DIR', 'country.xml'), nls_charset_id('AL32UTF8'))

           columns col1  number  path 'Id'

                 , names xmltype path 'NlTexts/Name'

         ) x1

       , XMLTable(

           '/Name'

           passing x1.names

           columns col2 varchar2(30) path '.'

                 --, col3 for ordinality

         ) x2

    ;

    (uncomment col3 to see what it does)

     

    or, in a shorter way :

    SELECT *

    FROM XMLTable(

           'for $i in /MAS/Country

              , $j in $i/NlTexts/Name

            return element r { $i/Id, $j }'

           passing XMLType(bfilename('TEST_DIR', 'country.xml'), nls_charset_id('AL32UTF8'))

           columns col1 number       path 'Id'

                 , col2 varchar2(30) path 'Name'

         ) ;

  • 2. Re: Parse XML file and extract data
    TimWong765 Newbie
    Currently Being Moderated

    odie_63, many thanks. Now I got it how to "parse" the XML file.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points