This content has been marked as final.
Show 3 replies
-
1. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
mNem Jul 13, 2019 3:44 AM (in response to Subramanyam Yalla-Oracle)FYI, I did remove the reference to dtd in my test case. The input xml content column name is also different.
-
2. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
mNem Jul 13, 2019 7:49 AM (in response to Subramanyam Yalla-Oracle)Assuming the xml content has a corresponding preceding <ata-header> element for each <mpd-sys-maint> element...
with temp_tab (xml) as ( select ' <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>' from dual ) select ata, RecordTasknbr, mpditemnbr from temp_tab , xmltable( ' /mpd/chapter/smits-section/mpd-sys-maints/mpd-sys-maint ' passing xmltype(xml) columns ata varchar2(100) path './preceding-sibling::*[1][local-name() = "ata-header"]' , RecordTasknbr varchar2(100) path '@tasknbr' , mpditemnbr varchar2(30) path 'mpd-item-nbr' ) x ;
ATA RECORDTASKNBR MPDITEMNBR ------------------------------ --------------- ------------------------------ ATA 12: SERVICING 12-002-01 12-002-01 ATA 20: STANDARD PRACTICES - A 20-010-00 20-010-00 IRFRAME