This discussion is archived
7 Replies Latest reply: Aug 12, 2011 12:15 AM by mh*379860*ly RSS

XQuery behaves ambiguously when extended

mh*379860*ly Newbie
Currently Being Moderated
HI Gentlemen,

I am confronted with a syntax problem again which I can not overcome. Given the following XML instance (only relevant part included:)
<?xml version="1.0" encoding="ISO-8859-15"?>
<ehd:ehd ehd_version="1.40" xmlns:ehd="urn:ehd/001" xmlns="urn:ehd/icd/001">
  <ehd:header>
    <ehd:id EX="f7c33598-5afb-4694-8aa9-be975dc6869b" RT="1.2.276.0.76.3.1.1"/>
    <ehd:document_type_cd V="ICD" DN="ICD-Stammdatei" S="1.2.276.0.76.5.100"/>
    <ehd:service_tmr V="2011-07-01..2011-12-31"/>
    <ehd:origination_dttm V="2011-05-02+02:00"/>
    <ehd:provider>
      <ehd:organization>
        <ehd:id EX="74" RT="1.2.276.0.76.5.233"/>
        <ehd:organization.nm V="KBV"/>
        <ehd:addr>
          <ehd:ADL V="Kassenärztliche Bundesvereinigung, Dezernat 3"/>
          <ehd:STR V="Herbert-Lewin-Platz"/>
          <ehd:HNR V="2"/>
          <ehd:ZIP V="10623"/>
          <ehd:CTY V="Berlin"/>
        </ehd:addr>
        <ehd:telecom V="tel:++49 30 4005 2121"/>
        <ehd:telecom V="mailto:KBVServiceDesk@KBV.de"/>
        <ehd:telecom V="http://www.KBV.de/ita/register_C.html"/>
      </ehd:organization>
    </ehd:provider>
    <ehd:interface>
      <ehd:id EX="SDICD" RT="1.2.276.0.76.5.109"/>
      <ehd:interface.nm V="ICD-10-GM Stammdatei"/>
      <ehd:version V="1.4"/>
      <ehd:description V="ICD-10-GM Stammdatei der KBV im XML-Format, Version 2011, COPYRIGHT-HINWEIS: Diese Datei wird den Softwarehäusern, die Arztpraxissoftware herstellen, sowie den Kassenärztlichen Vereinigungen vom Dezernat 6 der KBV ausschließlich zur Nutzung in der vertragsärztlichen Versorgung zur Verfügung gestellt. Für alle sonstigen Nutzungszwecke wende man sich über den Servicedesk der KBV (EMail: KBVServiceDesk@KBV.de, Telefon: 030 / 4005-2121) an das Dezernat 3 der KBV." URL="http://www.kbv.de/ita/register_C.html"/>
    </ehd:interface>
  </ehd:header>
  <ehd:body>
    <icd_stammdaten>
      <kapitel_liste>
        <kapitel>
          <nummer V="14"/>
          <von_icd_code V="N00"/>
          <bis_icd_code V="N99"/>
          <bezeichnung V="Krankheiten des Urogenitalsystems"/>
          <gruppen_liste>
            <gruppe>
              <von_icd_code V="N00"/>
              <bis_icd_code V="N08"/>
              <bezeichnung V="Glomeruläre Krankheiten"/>
              <diagnosen_liste>
                <diagnose>
                  <icd_code V="N00.-"/>
                  <bezeichnung V="Akutes nephritisches Syndrom"/>
                  <abrechenbar V="n"/>
                  <krankheit_in_mitteleuropa_sehr_selten V="n"/>
                  <schlüsselnummer_mit_inhalt_belegt V="j"/>
                  <infektionsschutzgesetz_meldepflicht V="n"/>
                  <infektionsschutzgesetz_abrechnungsbesonderheit V="n"/>
                  <diagnosen_liste>
                    <diagnose>
                      <icd_code V="N00.0"/>
                      <bezeichnung V="Akutes nephritisches Syndrom mit minimaler glomerulärer Läsion"/>
                      <abrechenbar V="j"/>
                      <krankheit_in_mitteleuropa_sehr_selten V="n"/>
                      <schlüsselnummer_mit_inhalt_belegt V="j"/>
                      <infektionsschutzgesetz_meldepflicht V="n"/>
                      <infektionsschutzgesetz_abrechnungsbesonderheit V="n"/>
                      <diagnosethesaurus_liste>
                        <diagnosethesaurus V="Glomerulusläsion"/>
                        <diagnosethesaurus V="Akutes nephritisches Syndrom mit minimaler glomerulärer Läsion"/>
                      </diagnosethesaurus_liste>
                      <kodierrichtlinien_liste>
                        <akr_ref V="B1400"/>
                        <akr_ref V="B1400p"/>
                      </kodierrichtlinien_liste>
                    </diagnose>
                    <diagnose>
                      <icd_code V="N00.1"/>
...
My task is to query several <diagnose> elements using <icd_code> as the key for them. The key is unique in the whole instance document. Note that I am stepping up one node from the key (<icd_code>) to its parent (<diagnose>).
Now I experience the follfowing:

(1) Basic XQuery statement--works fine
SQL> SELECT XMLSerialize(DOCUMENT
  2  XMLQuery(
  3    'xquery version "1.0"; (: :)
  4       declare namespace n1="urn:ehd/001"; (: :)
  5       declare namespace n2="urn:ehd/icd/001"; (: :)
  6       element h {
  7         /n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX
  8       , /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:diagnose/n2:icd_code[@V="&code"]/..}'
  9    PASSING xml_document
 10    RETURNING CONTENT
 11  )
 12  AS CLOB INDENT SIZE = 2
 13  ) as result
 14  FROM z
 15  where id = 11
 16  /
Geben Sie einen Wert für code ein: N08.8
alt   8:    , /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:diagnose/n2:icd_code[@V="&code"]/..}'
neu   8:    , /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:diagnose/n2:icd_code[@V="N08.8"]/..}'

RESULT                                                                                                                  
--------------------------------------------------------------------------------                                        
<h EX="74">                                                                                                             
  <diagnose xmlns="urn:ehd/icd/001">                                                                                    
    <icd_code V="N08.8"/>                                                                                               
    <bezeichnung V="Glomeruläre Krankheiten bei sonstigen anderenorts klassifizi                                        
erten Krankheiten  (I33.0+)"/>                                                                                          
    <abrechenbar V="j"/>                                                                                                
    <notationskennzeichen V="*"/>                                                                                       
    <krankheit_in_mitteleuropa_sehr_selten V="n"/>                                                                      
    <schlüsselnummer_mit_inhalt_belegt V="j"/>                                                                          
    <infektionsschutzgesetz_meldepflicht V="n"/>                                                                        
    <infektionsschutzgesetz_abrechnungsbesonderheit V="n"/>                                                             
    <kodierrichtlinien_liste>                                                                                           
      <akr_ref V="A06f"/>                                                                                               
      <akr_ref V="B1400"/>                                                                                              
    </kodierrichtlinien_liste>                                                                                          
  </diagnose>                                                                                                           
</h>                                                                                                                    
                                                                                                                        

1 Zeile wurde ausgewählt.
(2) XQuery statement extended with some additional elements--it will not convert to VARCHAR2, either
SQL> 
SQL> SELECT XMLSerialize(DOCUMENT
  2  XMLQuery(
  3    'xquery version "1.0"; (: :)
  4       declare namespace n1="urn:ehd/001"; (: :)
  5       declare namespace n2="urn:ehd/icd/001"; (: :)
  6       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
  7       element kap {element nummer {/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:nummer/@V} },
  8       element diagnose {/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:icd_code[@V="&code"]/..}'
  9    PASSING xml_document
 10    RETURNING CONTENT
 11  )
 12  AS clob INDENT SIZE = 2
 13  ) as result
 14  FROM z
 15  where id = 11
 16  /
Geben Sie einen Wert für code ein: N08.8
alt   8:    element diagnose {/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:icd_code[@V="&code"]/..}'
neu   8:    element diagnose {/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:icd_code[@V="N08.8"]/..}'
ERROR:
ORA-19012: XML-Fragment kann nicht in erforderlichen Datentyp konvertiert werden 

Es wurden keine Zeilen ausgewählt
(3) The same, extended further, but using $p for shorthand
SQL> SELECT XMLSerialize(DOCUMENT
  2  XMLQuery(
  3    'xquery version "1.0"; (: :)
  4       declare namespace n1="urn:ehd/001"; (: :)
  5       declare namespace n2="urn:ehd/icd/001"; (: :)
  6       let $p :=  /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
  7       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
  8       element kapitel {
  9            element nummer {$p/n2:nummer/@V},
 10            element von_icd_code {$p/n2:von_icd_code/@V},
 11            element bis_icd_code {$p/n2:bis_icd_code/@V},
 12            element bezeichnung {$p/n2:bezeichnung/@V}
 13                   },
 14       element gruppe {
 15            element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
 16            element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
 17            element bezeichnung {$p/n2:kapitel/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V}
 18                   },
 19       element diagnose {$p/descendant::n2:icd_code[@V="&code"]/..}'
 20    PASSING xml_document
 21    RETURNING CONTENT
 22  )
 23  AS CLOB INDENT SIZE = 2
 24  ) as result
 25  FROM z
 26  where id = 11
 27  /
Geben Sie einen Wert für code ein: N08.8
alt  19:    element diagnose {$p/descendant::n2:icd_code[@V="&code"]/..}'
neu  19:    element diagnose {$p/descendant::n2:icd_code[@V="N08.8"]/..}'
FROM z
     *
FEHLER in Zeile 25:
ORA-19114: XPST0003 - Fehler beim Parsen des XQuery-Ausdrucks:  
LPX-00801: XQuery syntax error at 'element' 
5      element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX }, 
-      ^ 
Could anyone tell me where I am wrong? By carefully studying DataDirect's XQUERY: A GUIDED TOUR I can not find a solution.

Thank you in advance, kind regards,
Miklos HERBOLY
  • 1. Re: XQuery behaves ambiguously when extended
    odie_63 Guru
    Currently Being Moderated
    Hi,

    There's nothing wrong with the 2nd XQuery.
    The problem is with XMLSerialize which expects an XML document (with a single root element), but you're passing an XML fragment.

    So, two possible solutions :
    - add a root element
    - or, use XMLSerialize( CONTENT ...) instead of XMLSerialize( DOCUMENT ...)

    For the 3rd XQuery, apart from the issue described above, you have to add a return statement and a sequence constructor.
    That's mandatory here since you've initiated a FLWOR structure with the let statement :
    xquery version "1.0"; (: :)
    declare namespace n1="urn:ehd/001"; (: :)
    declare namespace n2="urn:ehd/icd/001"; (: :)
    let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
    return (
      element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },     
      element kapitel {
        element nummer {$p/n2:nummer/@V},
        element von_icd_code {$p/n2:von_icd_code/@V},
        element bis_icd_code {$p/n2:bis_icd_code/@V},
        element bezeichnung {$p/n2:bezeichnung/@V}
      },
      element gruppe {
        element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
        element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
        element bezeichnung {$p/n2:kapitel/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V}
      },
      element diagnose {$p/descendant::n2:icd_code[@V="&code"]/..}
    )
    Note that I am stepping up one node from the key (<icd_code>) to its parent (<diagnose>).
    You don't have to do it that way. An XPath predicate testing the child node's attribute will work too.
    Something like this :
    /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/descendant::n2:diagnose[n2:icd_code/@V="&code"]
    Edited by: odie_63 on 8 août 2011 14:23
  • 2. Re: XQuery behaves ambiguously when extended
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Thank you very much for your highly appreciated help again. As you told me, version 2 behaves perfectly well with CONTENT. I modified version 3 according to what you proposed and it returned an interesting sequence of elements with no content, e g, <diagnose/> etc. Then I performed further modifications: I applied a where clause in XQuery instead qualifying icd_code, and adhered to the original structure of the document (see the code below). And now it works but always returns NULL (I made it visible by SET NULL ?). descendant is mandatory for me because the key (icd_code) comes in at two different levels as there are generic and specific ICD's.
    SELECT XMLSerialize(CONTENT
    XMLQuery(
      'xquery version "1.0"; (: :)
       declare namespace n1="urn:ehd/001"; (: :)
       declare namespace n2="urn:ehd/icd/001"; (: :)
       let $p :=  /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
       where $p/descendant::n2:diagnose/n2:icd_code/@V="&code"
       return (
       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
       element kapitel 
       {
         element nummer {$p/n2:nummer/@V},
         element von_icd_code {$p/n2:von_icd_code/@V},
         element bis_icd_code {$p/n2:bis_icd_code/@V},
         element bezeichnung {$p/n2:bezeichnung/@V},
         element gruppe 
         {
           element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
           element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
           element bezeichnung {$p/n2:kapitel/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V},
           element diagnose {$p/descendant::n2:diagnose}
         }
       }
    )'
      PASSING xml_document
      RETURNING CONTENT
    )
    AS CLOB INDENT SIZE = 2
    ) as result
    FROM z
    where id = 11
    /
    I would like to have a short answer if you don't mind, where I am still wrong. Otherwise, version 2 is completely satisfactory but a little bit hard to read.

    Thanks, kind regards,
    Miklos
  • 3. Re: XQuery behaves ambiguously when extended
    odie_63 Guru
    Currently Being Moderated
    I would like to have a short answer if you don't mind, where I am still wrong.
    Your query is correct, but there's a bug in the XQuery processor.
    I've been able to reproduce the issue in version 11.2.0.2.
    You may want to submit a SR to Oracle Support on that one.

    A possible workaround is to use the following hint :
    SELECT /*+ no_xml_query_rewrite */ 
           XMLSerialize(CONTENT
             XMLQuery( ...
    NO_XML_QUERY_REWRITE is a documented hint that forces the functional evaluation of the XQuery expression, thus disabling a possible optimization - if your storage model allows it (Object-Relational or Binary XML).

    The output should then look like this :
    <h EX="74"/>
    <kapitel>
      <nummer V="14"/>
      <von_icd_code V="N00"/>
      <bis_icd_code V="N99"/>
      <bezeichnung V="Krankheiten des Urogenitalsystems"/>
      <!-- more elements ... -->
    But maybe you want it like the following instead?
    <h EX="74"/>
    <kapitel>
      <nummer>14</nummer>
      <von_icd_code>N00</von_icd_code>
      <bis_icd_code>N99</bis_icd_code>
      <bezeichnung>Krankheiten des Urogenitalsystems</bezeichnung>
      <!-- more elements ... -->
    If so, you can do it with the data function, e.g. :
    element nummer {data($p/n2:nummer/@V)}
  • 4. Re: XQuery behaves ambiguously when extended
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Thanks for the good ideas. I will put a SR soon.
    What I am experiencing, wonders me.
    (1) When I adjust the query by eliminating the "step-to-parent" operation (code below), I get an empty result:
    SELECT XMLSerialize(CONTENT
    XMLQuery(
      'xquery version "1.0"; (: :)
       declare namespace n1="urn:ehd/001"; (: :)
       declare namespace n2="urn:ehd/icd/001"; (: :)
       let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
       return (
       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
       element kapitel 
       {
         element nummer {$p/n2:nummer/@V},
         element von_icd_code {$p/n2:von_icd_code/@V},
         element bis_icd_code {$p/n2:bis_icd_code/@V},
         element bezeichnung {$p/n2:bezeichnung/@V},
         element gruppe 
         {
           element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
           element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
           element bezeichnung {$p/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V},
           element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
         }
       }
    )'
      PASSING xml_document
      RETURNING CONTENT
    )
    AS clob INDENT SIZE = 2
    ) as result
    FROM z
    where id = 11
    /
    And the results are the following:
    SQL> @p21
    Geben Sie einen Wert für code ein: N80.8
    alt  20:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
    neu  20:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="N80.8"]}
    
    RESULT                                                                                                                  
    --------------------------------------------------------------------------------                                        
    <h EX="74"/>                                                                                                            
    <kapitel>                                                                                                               
      <nummer/>                                                                                                             
      <von_icd_code/>                                                                                                       
      <bis_icd_code/>                                                                                                       
      <bezeichnung/>                                                                                                        
      <gruppe>                                                                                                              
        <von_icd_code/>                                                                                                     
        <bis_icd_code/>                                                                                                     
        <bezeichnung/>                                                                                                      
        <diagnose/>                                                                                                         
      </gruppe>                                                                                                             
    </kapitel>                                                                                                              
                                                                                                                            
    
    1 Zeile wurde ausgewählt.
    
    SQL> spool off
    (2) When I return to the parent notation (code below), I get an error message
    SELECT XMLSerialize(CONTENT
    XMLQuery(
      'xquery version "1.0"; (: :)
       declare namespace n1="urn:ehd/001"; (: :)
       declare namespace n2="urn:ehd/icd/001"; (: :)
       let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
       return (
       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
       element kapitel 
       {
         element nummer {$p/n2:nummer/@V},
         element von_icd_code {$p/n2:von_icd_code/@V},
         element bis_icd_code {$p/n2:bis_icd_code/@V},
         element bezeichnung {$p/n2:bezeichnung/@V},
         element gruppe 
         {
           element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
           element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
           element bezeichnung {$p/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V},
           element diagnose {$p/descendant::n2:icd_code[@V="&code"]/..}
         }
       }
    )'
      PASSING xml_document
      RETURNING CONTENT
    )
    AS clob INDENT SIZE = 2
    ) as result
    FROM z
    where id = 11
    /
    And the results are:
    SQL> @p21
    Geben Sie einen Wert für code ein: N80.8
    alt  20:        element diagnose {$p/descendant::n2:icd_code[@V="&code"]/..}
    neu  20:        element diagnose {$p/descendant::n2:icd_code[@V="N80.8"]/..}
    ERROR:
    ORA-19121: Doppelte Attribut-Definition - V 
    
    
    
    Es wurden keine Zeilen ausgewählt
    
    SQL> spool off
    I really do not understand that it worked in version 2 above, where there were no nested elements. And here it claims for duplicates. I experimented with some combinations of nested elements in <gruppe> by taking them away by one: <diagnose> and <bezeichnung> would tolerate themselves. However, <von_icd_code> or <bis_icd_code> is running into duplicate attributes. Do you have an idea?

    Thanks, kind regards,
    Miklos

    Edited by: mh**** on Aug 9, 2011 6:30 AM
  • 5. Re: XQuery behaves ambiguously when extended
    odie_63 Guru
    Currently Being Moderated
    For (1), it works using NO_XML_QUERY_REWRITE hint.

    For (2), I don't reproduce the problem on 11.2.0.2.
    Could you remind me your database version?
  • 6. Re: XQuery behaves ambiguously when extended
    mh*379860*ly Newbie
    Currently Being Moderated
    It is a horror. The two files are completely identical except for the hint:
    Vergleichen der Dateien p21-hint.sql und P21-ORI.SQL
    ***** p21-hint.sql
    SELECT /*+ no_xml_query_rewrite */ 
    XMLSerialize(CONTENT
    XMLQuery(
    ***** P21-ORI.SQL
    SELECT XMLSerialize(CONTENT
    XMLQuery(
    *****
    Now, the original version 2 with child predicate does the following:
    SQL> @p21-ori
    SQL> SELECT XMLSerialize(CONTENT
      2  XMLQuery(
      3    'xquery version "1.0"; (: :)
      4       declare namespace n1="urn:ehd/001"; (: :)
      5       declare namespace n2="urn:ehd/icd/001"; (: :)
      6       let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
      7       return (
      8       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
      9       element kapitel
     10       {
     11         element nummer {$p/n2:nummer/@V},
     12         element von_icd_code {$p/n2:von_icd_code/@V},
     13         element bis_icd_code {$p/n2:bis_icd_code/@V},
     14         element bezeichnung {$p/n2:bezeichnung/@V},
     15         element gruppe
     16         {
     17           element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
     18           element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
     19           element bezeichnung {$p/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V},
     20           element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
     21         }
     22       }
     23  )'
     24    PASSING xml_document
     25    RETURNING CONTENT
     26  )
     27  AS clob INDENT SIZE = 2
     28  ) as result
     29  FROM z
     30  where id = 11
     31  /
    Geben Sie einen Wert für code ein: N80.8
    alt  20:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
    neu  20:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="N80.8"]}
    
    RESULT                                                                                                                  
    --------------------------------------------------------------------------------                                        
    <h EX="74"/>                                                                                                            
    <kapitel>                                                                                                               
      <nummer/>                                                                                                             
      <von_icd_code/>                                                                                                       
      <bis_icd_code/>                                                                                                       
      <bezeichnung/>                                                                                                        
      <gruppe>                                                                                                              
        <von_icd_code/>                                                                                                     
        <bis_icd_code/>                                                                                                     
        <bezeichnung/>                                                                                                      
        <diagnose/>                                                                                                         
      </gruppe>                                                                                                             
    </kapitel>                                                                                                              
                                                                                                                            
    
    1 Zeile wurde ausgewählt.
    
    SQL> spool off
    And version 2 with the proposed hint behaves like this:
    SQL> @p21-hint
    SQL> SELECT /*+ no_xml_query_rewrite */
      2  XMLSerialize(CONTENT
      3  XMLQuery(
      4    'xquery version "1.0"; (: :)
      5       declare namespace n1="urn:ehd/001"; (: :)
      6       declare namespace n2="urn:ehd/icd/001"; (: :)
      7       let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
      8       return (
      9       element h {/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX },
     10       element kapitel
     11       {
     12         element nummer {$p/n2:nummer/@V},
     13         element von_icd_code {$p/n2:von_icd_code/@V},
     14         element bis_icd_code {$p/n2:bis_icd_code/@V},
     15         element bezeichnung {$p/n2:bezeichnung/@V},
     16         element gruppe
     17         {
     18           element von_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:von_icd_code/@V},
     19           element bis_icd_code {$p/n2:gruppen_liste/n2:gruppe/n2:bis_icd_code/@V},
     20           element bezeichnung {$p/n2:gruppen_liste/n2:gruppe/n2:bezeichnung/@V},
     21           element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
     22         }
     23       }
     24  )'
     25    PASSING xml_document
     26    RETURNING CONTENT
     27  )
     28  AS clob INDENT SIZE = 2
     29  ) as result
     30  FROM z
     31  where id = 11
     32  /
    Geben Sie einen Wert für code ein: N80.8
    alt  21:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="&code"]}
    neu  21:        element diagnose {$p/descendant::n2:diagnose[n2:icd_code/@V="N80.8"]}
    ERROR:
    ORA-19121: Doppelte Attribut-Definition -  
    
    
    
    Es wurden keine Zeilen ausgewählt
    
    SQL> spool off
    I am completely confused.

    My database version is 11.2.0.2.0

    Thanks, kind regards,
    Miklos
  • 7. Re: XQuery behaves ambiguously when extended
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Thank you for all that. My database version is 11.2.0.2.0. I put a short reply to your last addendum for the case you have time. My database has been installed directly from the patch be previously uninstalling and deleting every bit of the predecessor.

    Kind regards,
    Miklos

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points