Hi,
I wrote below code to extract data from xml data file.
select ata, mpditemnbr
from
temp_tab ,
xmltable(
'/mpd/chapter/smits-section'
passing xmltype(file_data,1)
columns
FirstRecord xmltype path 'mpd-sys-maints'
),
xmltable(
'mpd-sys-maints'
passing FirstRecord
columns
ata varchar2(100) path 'ata-header',
Record xmltype path 'mpd-sys-maint'),
xmltable(
'mpd-sys-maint'
passing Record
columns
RecordTasknbr varchar2(100) path '@tasknbr',
mpditemnbr varchar2(30) path 'mpd-item-nbr'
)
I am getting below error when executed this query:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
Please advise what mistake I am doing in the query.
xml data file is given below:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mpd SYSTEM "mpboe04.dtd">
<mpd spl="81205" model="777-200/300" oidate="01.JAN.1995" revdate="05.JAN.2019" tsn="019" docnbr="D622W001XMLSUP" lang="EN">
<chapter key="JEFD20796A1B2319E206D4AF2E0F7F84" fragment="1" subfragment="0">
<title>SYSTEM MAINTENANCE PROGRAM</title>
<smits-section key="LF3D50E082B92E7E54E5161A661A3A99" fragment="1" subfragment="1">
<title>SYSTEMS AND POWERPLANT MAINTENANCE PROGRAM</title>
<mpd-sys-maints chapter-id="CH1">
<ata-header>ATA 12: SERVICING</ata-header>
<mpd-sys-maint tasknbr="12-002-01">
<mpd-item-nbr>12-002-01</mpd-item-nbr>
</mpd-sys-maint>
<ata-header>ATA 20: STANDARD PRACTICES - AIRFRAME</ata-header>
<mpd-sys-maint tasknbr="20-010-00">
<mpd-item-nbr>20-010-00</mpd-item-nbr>
</mpd-sys-maint>
</mpd-sys-maints>
</smits-section>
</chapter>
</mpd>