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

weird behaviour xml query

HenkB Newbie
Currently Being Moderated


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

Legend

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