CODEWITHSUNDEEP
xmloracle-databaseplsqlxquery
Mick Greener
Mick Greener

Reputation: 93

Error message ORA-19279: XPTY0004 received when trying to create view of XML file in PL/SQL

I am trying to create a view of some XML files in Oracle PL/SQL. The problem with the data content of the XML files is that a single path often has more than one data entry, as shown below:

<priip>
  <data>
    <properties>
      <priipMonitoringInterval>daily</priipMonitoringInterval>
      <priipMonitoringStartDate>2017-06-30T13:51:03.168000</priipMonitoringStartDate>
      <permittedKIDDownloadJurisdictions>
        <item>DE</item>
        <item>AT</item>
      </permittedKIDDownloadJurisdictions>
    </properties>
  </data>
</priip>

I am hence getting an error message which reads:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

Here, clearly, the problem lies with the path:

priip/data/properties/permittedKIDDownloadJurisdictions/item

where we have two permissible jurisdictions, DE and AT. I cannot change the raw XML file, and I require all data within the files to be entered into the PL/SQL view. I have attached a sample of my code below, showing what I have already tried to do.

create or replace view PRIIPS_XML_VIEW as
  select *
    from PRIIPS_XML t,
      XMLTable(
        '$p/priip/data'
        passing t.SYS_NC_ROWINFO$ as "p"

        columns
          priip_monitoring_interval varchar2(20) path 'properties/priipMonitoringInterval',
          priip_monitoring_start_date varchar2(26) path 'properties/priipMonitoringStartDate',
          kid_download_jurisdiction varchar2(10) path 'properties/permittedKIDDownloadJurisdictions/item'
          );

Any help would be greatly appreciated!

Upvotes: 0

Views: 2010

Answers (2)

mark d drake
mark d drake

Reputation: 1505

Since Item occurs more than once it should be dealt with using a second XMLTABLE operations. This will give a normalized view of the item content.

See https://livesql.oracle.com/apex/livesql/file/content_E8GUH32UJN0EDKXRSV30ZUX4V.html

Upvotes: 3

OldProgrammer
OldProgrammer

Reputation: 12169

Change to this. It is fragile and will not work for > 2 item elements, which in that case you will want to use xquery to do some more sophisticated stuff:

create or replace view PRIIPS_XML_VIEW as
  select *
    from PRIIPS_XML t,
      XMLTable(
        '$p/priip/data'
        passing t.SYS_NC_ROWINFO$ as "p"

        columns
          priip_monitoring_interval varchar2(20) path 'properties/priipMonitoringInterval',
          priip_monitoring_start_date varchar2(26) path 'properties/priipMonitoringStartDate',
          kid_download_jurisdiction varchar2(10) path       
 kid_download_jurisdiction1 varchar2(10) path 'properties/permittedKIDDownloadJurisdictions/item[1]',
          kid_download_jurisdiction2 varchar2(10) path 'properties/permittedKIDDownloadJurisdictions/item[2]' 
);

Upvotes: 2

Related Questions