2 Replies Latest reply: Jul 22, 2013 6:56 AM by TimWong765 RSS

    Parse XML file and extract data

    TimWong765

      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

          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

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