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

Question

I'm quite new to XML and I hope to list all relevant information here. If not, please don't send negative feedback. I appreciate if you let me know what information you are missing.

I fail in getting below query running properly on multiple elements in a XML View on a Oracle Database 11g.

I have multiple XML files which are structured like below:

<Qualitaetsbericht>
    <Organisationseinheiten_Fachabteilungen>
         <Organisationseinheiten_Fachabteilung>
               <Fachabteilungsschluessel>
               <Prozeduren>
                  <Freiwillig>
                  <Verpflichtend>
                      <Prozedur>
                         <OPS_301>
                         <Anzahl>

I get usually proper result with below query:

SELECT id, 
       d."FA_SCHLUESSEL",d."OPS_301",
       CASE WHEN d."ANZAHL" IS NULL THEN '4' ELSE d."ANZAHL" END AS ANZAHL,
       d."GLIEDERUNGSNUMMER",d."NAME"
FROM   XMLDocs x,
XMLTable(
'/Data'
PASSING XMLQuery(
'for $i in /Qualitaetsbericht./Organisationseinheiten_Fachabteilungen/Organisationseinheit_Fachabteilung/Prozeduren/Verpflichtend/Prozedur
return <Data>
                 {$i/OPS_301}
                 {$i/Anzahl}
                 {$i/../../../Fachabteilungsschluessel/FA_Schluessel}
                 {$i/../../../Gliederungsnummer}
{$i/../../../Name}
</Data>'
PASSING doc
RETURNING CONTENT
)
COLUMNS FA_Schluessel varchar2(12) path 'FA_Schluessel',
        OPS_301      varchar2(12) path 'OPS_301',
        Anzahl        varchar2(40) path 'Anzahl',
        Gliederungsnummer varchar2(10) path 'Gliederungsnummer',
        Name varchar2(600) path 'Name'
) d

But there is one additional element which can occure

If this Element is given in the XML, my above query runs into the dynamic type mismatch error.

I figured out that

<Fachabteilungsschluessel> 

bears either this structure

<Fachabteilungsschluessel>
   <FA_Schluessel>

or

<Fachabteilungsschluessel>
   <Sonstiger>
       <FA_Sonstiger_Schluessel>

Whenever

<Fachabteilungsschluessel>
   <Sonstiger>
       <FA_Sonstiger_Schluessel>

Appears in the XML Document I get the error code:

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

I hope this is an easy to solve issue. Currently I'm running out of ideas. Can somebody help please? -Thanks in advance.

Example file here:
file

Solution

The problem is with duplicated nodes. In your second Organisationseinheit_Fachabteilung node (which has Gliederungsnummer of 2) there are two Fachabteilungsschluessel nodes, each with a FA_Schluessel - 0193 and 0300.

In your XMLQuery you're starting down at the Prozedur nodes, and then referring back up the structure to find ../../../Fachabteilungsschluessel/FA_Schluessel. In this case, that finds two matches - i.e. multiple items where a single one is expected.

You could adjust your XMLQuery's XPath to use nested loops, but it gets a bit messy and hard to follow, and is also quite slow in my testing with your 2.5MB file:

...
PASSING XMLQuery(
'for $i in /Qualitaetsbericht/Organisationseinheiten_Fachabteilungen/Organisationseinheit_Fachabteilung
for $j in $i/Prozeduren/Verpflichtend/Prozedur
return <Data>
                 {$j/OPS_301}
                 {$j/Anzahl}
                 {$i/Fachabteilungsschluessel/FA_Schluessel}
                 {$i/Gliederungsnummer}
                 {$i/Name}
</Data>'
PASSING doc
RETURNING CONTENT
)
...

although you don't need a separate XMLQuery in there, the XMLTable's XPath can do all that itself instead.

You can use multiple levels of XMLTable instead:

SELECT xd.id, 
       x3.fa_schluessel,
       x2.ops_301,
       CASE WHEN x2.anzahl IS NULL THEN '4' ELSE x2.anzahl END AS anzahl,
       x1.gliederungsnummer,
       x1.name
FROM   XMLDocs xd
CROSS JOIN XMLTable(
  '/Qualitaetsbericht/Organisationseinheiten_Fachabteilungen/Organisationseinheit_Fachabteilung'
  PASSING doc
  COLUMNS gliederungsnummer varchar2(10) path 'Gliederungsnummer',
          name varchar2(600) path 'Name',
          prozeduren XMLType path 'Prozeduren',
          fa_schluessel XMLType path 'Fachabteilungsschluessel/FA_Schluessel'
) x1
CROSS JOIN XMLTable(
  '/Prozeduren/Verpflichtend/Prozedur'
  PASSING x1.prozeduren
  COLUMNS ops_301 varchar2(12) path 'OPS_301',
          anzahl varchar2(40) path 'Anzahl'
) x2
CROSS JOIN XMLTable(
  '/FA_Schluessel'
  PASSING x1.fa_schluessel
  COLUMNS fa_schluessel varchar2(12) path '.'
) x3
/

which with your sample data file gets:

        ID FA_SCHLUESSE OPS_301      ANZAHL                                   GLIEDERUNG NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- ------------ ------------ ---------------------------------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        42 0100         1-204.2      4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-207.0      5                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-208.3      4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-208.6      4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-266.0      4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-610.2      4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         1-844        4                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         3-052        5                                        1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        42 0100         3-200        67                                       1          Notfallmedizin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
...

The first XMLTable gets the data directly under the Organisationseinheit_Fachabteilun nodes, and also gets the child Prozeduren and 'Fachabteilungsschluessel/FA_Schluessel` fragments for each node as separate XMLTypes, which can have multiple nodes. Those fragments are then passed in to the second and third XMLTables, which extract the lower-level data.

With this model there can be multiple parent nodes, each with multiple child nodes.

If you have another repeatng node you can extract that as another XMLType fragment, and add a fourth XMLTable:

...
  COLUMNS gliederungsnummer varchar2(10) path 'Gliederungsnummer',
          name varchar2(600) path 'Name',
          prozeduren XMLType path 'Prozeduren',
          fa_schluessel XMLType path 'Fachabteilungsschluessel/FA_Schluessel',
          fa_sonstiger_schluessel XMLType
            path 'Fachabteilungsschluessel/Sonstiger/FA_Sonstiger_Schluessel'
) x1
...
CROSS JOIN XMLTable(
  '/FA_Sonstiger_Schluessel'
  PASSING x1.fa_sonstiger_schluessel
  COLUMNS fa_sonstiger_schluessel varchar2(12) path '.'
) x4

... and can then use x4.fa_sonstiger_schluessel in your main select list.

Performance may begin to suffer as you add more cross joins though.