3 Replies Latest reply: Aug 4, 2013 8:55 AM by HenkB Branched to a new discussion. RSS

    weird behaviour xml query

    HenkB


      Hi,

       

      I have the following setup: (10gr2 and 11R2)

       

      CREATE TABLE cas_nummers OF XMLType;

      insert into cas_nummers
        VALUES (XMLType(bfilename('CAS_XML', 'test1.xml'),
                nls_charset_id('AL32UTF8')));

       

      test1.xml

      <?xml version="1.0" encoding="utf-8"?>

      <ECSubstanceInventory xmlns="http://echa.europa.eu/schemas/ecInventory" scope="complete" schemaVersion="2.1" xml:lang="en">

      <versionInfo>

        <version>1.2</version>

        <date>2012-1-27T11:38:11Z</date>

      </versionInfo>

      <ecSubstances>

        <ECSubstance creationDate="1990-06-15T03:00:00Z" status="active">

         <ecNumber>200-579-1</ecNumber>

         <ecNames>

          <ecName>formic acid</ecName>

         </ecNames>

         <casNumber>64-18-6</casNumber>

         <molecularFormula>CH2O2</molecularFormula>

        </ECSubstance>

        <ECSubstance creationDate="1990-06-15T03:00:00Z" status="active">

         <ecNumber>200-827-9</ecNumber>

         <ecNames>

          <ecName>propane</ecName>

         </ecNames>

         <casNumber>74-98-6</casNumber>

         <molecularFormula>C3H8</molecularFormula>

        </ECSubstance>

        <ECSubstance creationDate="1990-06-15T03:00:00Z" status="active">

         <ecNumber>201-172-1</ecNumber>

         <ecNames>

          <ecName>propionamide</ecName>

         </ecNames>

         <casNumber>79-05-0</casNumber>

         <molecularFormula>C3H7NO</molecularFormula>

        </ECSubstance>

        <ECSubstance creationDate="1990-06-15T03:00:00Z" status="active">

         <ecNumber>201-333-6</ecNumber>

         <ecNames>

          <ecName>2-nitro-m-xylene</ecName>

         </ecNames>

         <casNumber>81-20-9</casNumber>

         <molecularFormula>C8H9NO2</molecularFormula>

        </ECSubstance>

      </ecSubstances>

      </ECSubstanceInventory>

       

      I have made the following query:

      select a.*, b.*

         from (select object_value xml from cas_nummers),

              xmltable

              (

                       '/ECSubstanceInventory/ecSubstances/ECSubstance'

                       passing xml

                       columns

                       creationDate varchar2(30) path '@creationDate',

                       status varchar2(20) path '@status',

                       ecnumber  varchar2(20) path '/ECSubstanceInventory/ecSubstances/ECSubstance/ecNumber',

                       casnumber varchar2(20) path '/ECSubstanceInventory/ecSubstances/ECSubstance/casNumber',

                       molecularFormula varchar2(20) path '/ECSubstanceInventory/ecSubstances/ECSubstance/molecularFormula',

                       namelist xmltype path '/ECSubstanceInventory/ecSubstances/ECSubstance/ecNames'

              ) a,

              xmltable

              (

                       '/ECSubstanceInventory/ecSubstances/ECSubstance'

                       passing a.namelist

                       columns

                       ecName      varchar2(5) path 'ecNames'

              ) b

      but the query gives 0 rows...

      I tried also with xmlquery

      select  xmlquery('$p/ECSubstanceInventory/ecSubstances/ECSubstance/ecNames/ecName/text()'

                               passing object_value as "p" returning content) as col1

      from cas_nummers x;

       

      with the same 0 result...

       

      Any idea what is wrong with the xml or my query?

       

      Thanks in advance,

       

      Henk