title: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
body: <p>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.</p>
  
  <p>I fail in getting below query running properly on multiple elements in a XML View on a Oracle Database 11g.</p>
  
  <p>I have multiple XML files which are structured like below:</p>
  
  <pre><code>&lt;Qualitaetsbericht&gt;
      &lt;Organisationseinheiten_Fachabteilungen&gt;
           &lt;Organisationseinheiten_Fachabteilung&gt;
                 &lt;Fachabteilungsschluessel&gt;
                 &lt;Prozeduren&gt;
                    &lt;Freiwillig&gt;
                    &lt;Verpflichtend&gt;
                        &lt;Prozedur&gt;
                           &lt;OPS_301&gt;
                           &lt;Anzahl&gt;
  </code></pre>
  
  <p>I get usually proper result with below query:</p>
  
  <pre><code>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 &lt;Data&gt;
                   {$i/OPS_301}
                   {$i/Anzahl}
                   {$i/../../../Fachabteilungsschluessel/FA_Schluessel}
                   {$i/../../../Gliederungsnummer}
  {$i/../../../Name}
  &lt;/Data&gt;'
  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
  </code></pre>
  
  <p>But there is one additional element which can occure
      </p>
  
  <p>If this Element is given in the XML, my above query runs into the dynamic type mismatch error.</p>
  
  <p>I figured out that </p>
  
  <pre><code>&lt;Fachabteilungsschluessel&gt; 
  </code></pre>
  
  <p>bears either this structure</p>
  
  <pre><code>&lt;Fachabteilungsschluessel&gt;
     &lt;FA_Schluessel&gt;
  </code></pre>
  
  <p>or</p>
  
  <pre><code>&lt;Fachabteilungsschluessel&gt;
     &lt;Sonstiger&gt;
         &lt;FA_Sonstiger_Schluessel&gt;
  </code></pre>
  
  <p>Whenever </p>
  
  <pre><code>&lt;Fachabteilungsschluessel&gt;
     &lt;Sonstiger&gt;
         &lt;FA_Sonstiger_Schluessel&gt;
  </code></pre>
  
  <p>Appears in the XML Document I get the error code:</p>
  
  <pre><code>ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
  </code></pre>
  
  <p>I hope this is an easy to solve issue. Currently I'm running out of ideas. Can somebody help please? -Thanks in advance.</p>
  
  <p>Example file here: <br>
  <a href="https://wetransfer.com/downloads/e7c8ed274c03138b36a04f52be994e8020170222125547/141dd4" rel="nofollow noreferrer">file</a></p>