This discussion is archived
12 Replies Latest reply: Sep 21, 2011 5:30 AM by mh*379860*ly RSS

Syntax error when using "let"

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

I have a small XQuery file which works A1 if I do not use let $p... However, the schema is nested deeply and I tried to do it for readability's sake. Now I always get a syntax error--it will not put up with "let" and "element" together. Below is a trial for just one line. By removing $p and re-substituting its content it is OK.
<?xml version="1.0" encoding='windows-1252'?>
<!--<?xml-stylesheet type="text/xsl" href="icd.xsl"?>-->
<page xmlns:xsql="urn:oracle-xsql"  connection="gksconnection">
<xsql:include-xml xmlns:xsql="urn:oracle-xsql">
SELECT XMLSerialize(DOCUMENT
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

   element h 
   {  
     /n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX,
     element kap 
     {
       $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:nummer,
       /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
       /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code,
       /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code,
       element group 
       {
          /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
          /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code, 
          /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code, 
          element diag
          {
             /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V="{@icd_code}"]
          }
       }
     }
   }'
  PASSING xml_document
  RETURNING CONTENT
)
AS CLOB INDENT SIZE = 2
) as result
FROM icd
where id = {@idno} 
</xsql:include-xml>
</page>
Can anyone help me in finding the cause of the error? Unfortunately, I need some "header" data from <kapitel> and <group> too, which lies above the "core" of the query: the <diagnose> node. For this reason I chose the qualifying technique.

Thank you very much, kind regards

Miklos HERBOLY
  • 1. Re: Syntax error when using "let"
    odie_63 Guru
    Currently Being Moderated
    Hi Miklos,

    If you want to use "let" then you're beginning a XQuery FLWOR expression (L standing for "let"), but you're missing the R (= "return").

    Please try :
    ...
    let $p := /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
    return element h 
    ...
  • 2. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie_63,

    Many thanks again for your kindly corrective action. I just tried it and it worked fine--no syntax error any longer. However, it may have a side effect that I do not understand. Please look at the following xsql file:
    <?xml version="1.0" encoding='windows-1252'?>
    <?xml-stylesheet type="text/xsl" href="icd.xsl"?>
    <page xmlns:xsql="urn:oracle-xsql"  connection="gksconnection">
    <xsql:include-xml xmlns:xsql="urn:oracle-xsql">
    SELECT XMLSerialize(DOCUMENT
    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,
         /n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@RT,
         element kap 
         {
           /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:nummer,
           $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
           $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code,
           $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code,
           element group 
           {
              /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
              /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code, 
              /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code, 
              /n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V="{@icd_code}"]
           }
         }
       }'
      PASSING xml_document
      RETURNING CONTENT
    )
    AS CLOB INDENT SIZE = 2
    ) as result
    FROM icd
    where id = {@idno} 
    </xsql:include-xml>
    </page>
    Here, all but the first element in kap is expressed in terms of *$p*. And the corresponding stylesheet does not render them. Here is a portion of the stylesheet responsible:
      <table border="1">
      <tr>
      <td>
    
      <ul style="margin-top: 0px;padding-left: 0px;">
      <xsl:for-each select="kap">
        <li><b>Kapitel: </b><xsl:value-of select="pe:nummer/@V"/>
            <xsl:text> - </xsl:text>
            <xsl:value-of select="pe:bezeichnung/@V"/>
        </li>
    
        <ul class="none">
          <li><xsl:text>ICD-Codes: </xsl:text>
              <xsl:value-of select="pe:von_icd_code/@V"/>
              <xsl:text> bis </xsl:text>
              <xsl:value-of select="pe:bis_icd_code/@V"/>
          </li>
        </ul>
    That is, only pe:nummer is rendered, with the rest skipped. Note that the original document has namespace n2 for the body section, so I defined pe as the corresponding one in the stylesheet. Do you have an idea what is still wrong?

    Thanks, kind regards
    Miklos
  • 3. Re: Syntax error when using "let"
    odie_63 Guru
    Currently Being Moderated
    Is it possible you post an instance document to reproduce the issue?

    I suspect another bug in the XQuery optimizer.
    Could you try adding the following hint?
    SELECT /*+ NO_XML_QUERY_REWRITE */ 
           XMLSerialize(DOCUMENT
             XMLQuery(
               ...
  • 4. Re: Syntax error when using "let"
    tsuji Journeyer
    Currently Being Moderated
    Probably this. Details might need some further fine tuning: the idea is there.
    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,
         /n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@RT,
         for $q in $p
         return
         {
         element kap 
         {
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:nummer,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code,
           element group 
           {
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bezeichnung,
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:von_icd_code, 
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="{@icd_code}"]/n2:bis_icd_code, 
              $q/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V="{@icd_code}"]
           }
           }
         }
       }'
  • 5. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Thank you again. Here is an abridged version of the instance document. Note that the content is repeating thousands of times, and the whole document is ca. 18 MB. The structure is kept unchanged; I only left diagnose A00.- and its subordinates. You can play as fast and loose with $p as you like. Tag <diagnose> is repeating as there is always a "leading diagnose" with minus sign which has its numbered subordinates. XSQL must find both by one, that's why I included descendant.
    <?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="1"/>
              <von_icd_code V="A00"/>
              <bis_icd_code V="B99"/>
              <bezeichnung V="Bestimmte infektiöse und parasitäre Krankheiten"/>
              <gruppen_liste>
                <gruppe>
                  <von_icd_code V="A00"/>
                  <bis_icd_code V="A09"/>
                  <bezeichnung V="Infektiöse Darmkrankheiten"/>
                  <diagnosen_liste>
                    <diagnose>
                      <icd_code V="A00.-"/>
                      <bezeichnung V="Cholera"/>
                      <abrechenbar V="n"/>
                      <krankheit_in_mitteleuropa_sehr_selten V="j"/>
                      <schlüsselnummer_mit_inhalt_belegt V="j"/>
                      <infektionsschutzgesetz_meldepflicht V="j"/>
                      <infektionsschutzgesetz_abrechnungsbesonderheit V="j"/>
                      <diagnosen_liste>
                        <diagnose>
                          <icd_code V="A00.0"/>
                          <bezeichnung V="Cholera durch Vibrio cholerae O:1, Biovar cholerae"/>
                          <abrechenbar V="j"/>
                          <krankheit_in_mitteleuropa_sehr_selten V="j"/>
                          <schlüsselnummer_mit_inhalt_belegt V="j"/>
                          <infektionsschutzgesetz_meldepflicht V="j"/>
                          <infektionsschutzgesetz_abrechnungsbesonderheit V="j"/>
                          <diagnosethesaurus_liste>
                            <diagnosethesaurus V="Klassische Cholera"/>
                          </diagnosethesaurus_liste>
                          <kodierrichtlinien_liste>
                            <akr_ref V="B1101"/>
                          </kodierrichtlinien_liste>
                        </diagnose>
                        <diagnose>
                          <icd_code V="A00.1"/>
                          <bezeichnung V="Cholera durch Vibrio cholerae O:1, Biovar eltor"/>
                          <abrechenbar V="j"/>
                          <krankheit_in_mitteleuropa_sehr_selten V="j"/>
                          <schlüsselnummer_mit_inhalt_belegt V="j"/>
                          <infektionsschutzgesetz_meldepflicht V="j"/>
                          <infektionsschutzgesetz_abrechnungsbesonderheit V="j"/>
                          <diagnosethesaurus_liste>
                            <diagnosethesaurus V="El-Tor-Cholera"/>
                            <diagnosethesaurus V="Choleraartige Dysenterie"/>
                            <diagnosethesaurus V="El-Tor-Enteritis"/>
                          </diagnosethesaurus_liste>
                          <kodierrichtlinien_liste>
                            <akr_ref V="B1101"/>
                          </kodierrichtlinien_liste>
                        </diagnose>
                        <diagnose>
                          <icd_code V="A00.9"/>
                          <bezeichnung V="Cholera, nicht näher bezeichnet"/>
                          <abrechenbar V="j"/>
                          <krankheit_in_mitteleuropa_sehr_selten V="j"/>
                          <schlüsselnummer_mit_inhalt_belegt V="j"/>
                          <infektionsschutzgesetz_meldepflicht V="j"/>
                          <infektionsschutzgesetz_abrechnungsbesonderheit V="j"/>
                          <diagnosethesaurus_liste>
                            <diagnosethesaurus V="Cholera"/>
                            <diagnosethesaurus V="Cholera maligna"/>
                            <diagnosethesaurus V="Cholera asiatica"/>
                            <diagnosethesaurus V="Cholera epidemica"/>
                            <diagnosethesaurus V="Cholera durch Vibrio cholerae O:1"/>
                          </diagnosethesaurus_liste>
                          <kodierrichtlinien_liste>
                            <akr_ref V="A06f"/>
                            <akr_ref V="B1101"/>
                          </kodierrichtlinien_liste>
                        </diagnose>
                      </diagnosen_liste>
                      <kodierrichtlinien_liste>
                        <akr_ref V="B1101"/>
                      </kodierrichtlinien_liste>
                    </diagnose>
                  </diagnosen_liste>
                </gruppe>
              </gruppen_liste>
            </kapitel>
          </kapitel_liste>
        </icd_stammdaten>
      </ehd:body>
    </ehd:ehd>
    Thanks, kind regards,

    Miklos

    Edited by: mh**** on Sep 18, 2011 1:13 AM

    Edited by: mh**** on Sep 18, 2011 1:14 AM
  • 6. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    HI tsuji,

    Many thanks for your contribution. I tried to execute the corrected version but ran into another syntax error: it complained about an opening brace. Here is the output redirected to a log file.
    Do you have an idea where I am wrong?
    SELECT XMLSerialize(DOCUMENT
    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,
         /n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@RT,
         for $q in $p
         return
         {
         element kap 
         {
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:nummer,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:bezeichnung,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:von_icd_code,
           $q[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:bis_icd_code,
           element group 
           {
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:bezeichnung,
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:von_icd_code, 
              $q/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V="A00.0"]/n2:bis_icd_code, 
              $q/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V="A00.0"]
           }
           }
         }
       }'  
      PASSING xml_document
      RETURNING CONTENT
    )
    AS CLOB INDENT SIZE = 2
    ) as result
    FROM icd
    where id = 99ORA-19114: XPST0003 - Fehler beim Parsen des XQuery-Ausdrucks: 
    LPX-00801: XQuery syntax error at '{'
    12        {
    -         ^
    Thank, kind regards,

    Miklos
  • 7. Re: Syntax error when using "let"
    tsuji Journeyer
    Currently Being Moderated
    Hi mh, if there leads to syntax error, maybe I would ask you to debug on your side such as taking out the pair of curly brackets I might erroneously put to wrap the "element kap" etc, as I posted under a fairly big cloud of uncertainty surrounding the issue that prevents me from focusing on all the details.

    May be I can just state the central idea for a development such as I posted. In the $p, there is no guarantee that it results in a sequence of only one (if not none) entry. If there were more, you should pick them up one by one. If not, chances are something like what you'd reported as "...all but the first element in kap is expressed in terms of $p..." may happen: but I must say, the exact meaning of what you said is not completely clear to me lacking illustration. In any case, you've to provision a security net for the case of $p with more entries...

    And then, within the element kap and element group constructors, there appears to have plenty of paths nowhere referencing the context of $p (or as I suggested specific entry in the sequence of $p). That seems they are not moving in synch with $p. The lack of mobility of context is disturbing and I reckon it might be wrong. Hence, I introduced back the context reference as reflected in $q (a specific entry of $p, if any) replacing those part of the absolute path... I cannot be 100% sure, though. You've to pick up the general idea and do something in that direction, if the reasoning director is valid...

    I'm afraid I can only offer this interpretation on the problem as I see it.
  • 8. Re: Syntax error when using "let"
    odie_63 Guru
    Currently Being Moderated
    Miklos,

    If I recall correctly you're on 11.2.0.2, so implicitly using Binary XML storage.

    I've tried to reproduce the case on 11.2.0.1, like this :
    create table icd (
     id number,
     xml_document xmltype
    )
    xmltype xml_document store as securefile binary xml
    ;
    
    insert into icd values(1, xmltype(bfilename('XML_DIR', 'icd.xml'), nls_charset_id('WEISO8859P15')));
    The query appears to work correctly on that version, even when using the $p variable everywhere.
    For example, the following query :
    SELECT XMLSerialize(DOCUMENT
      XMLQuery(
       'xquery version "1.0"; (: :)
        declare namespace n1="urn:ehd/001"; (: :)
        declare namespace n2="urn:ehd/icd/001"; (: :)
        let $p := $d/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
        return element h
        {
          $d/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX,
          $d/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@RT,
          element kap
          {
            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:nummer,
            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bezeichnung,
            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:von_icd_code,
            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bis_icd_code,
            element group
            {
               $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bezeichnung,
               $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:von_icd_code,
               $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bis_icd_code,
               $p/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V=$icd_code]
            }
          }
        }'
        PASSING xml_document as "d", 'A00.1' as "icd_code"
        RETURNING CONTENT
      )
      AS CLOB INDENT SIZE = 2
    ) as result
    FROM icd
    ;
    gives :
    <h EX="74" RT="1.2.276.0.76.5.233">
      <kap>
        <nummer xmlns="urn:ehd/icd/001" V="1"/>
        <bezeichnung xmlns="urn:ehd/icd/001" V="Bestimmte infektiöse und parasitäre Krankheiten"/>
        <von_icd_code xmlns="urn:ehd/icd/001" V="A00"/>
        <bis_icd_code xmlns="urn:ehd/icd/001" V="B99"/>
        <group>
          <bezeichnung xmlns="urn:ehd/icd/001" V="Infektiöse Darmkrankheiten"/>
          <von_icd_code xmlns="urn:ehd/icd/001" V="A00"/>
          <bis_icd_code xmlns="urn:ehd/icd/001" V="A09"/>
          <diagnose xmlns="urn:ehd/icd/001">
            <icd_code V="A00.1"/>
            <bezeichnung V="Cholera durch Vibrio cholerae O:1, Biovar eltor"/>
            <abrechenbar V="j"/>
            <krankheit_in_mitteleuropa_sehr_selten V="j"/>
            <schlüsselnummer_mit_inhalt_belegt V="j"/>
            <infektionsschutzgesetz_meldepflicht V="j"/>
            <infektionsschutzgesetz_abrechnungsbesonderheit V="j"/>
            <diagnosethesaurus_liste>
              <diagnosethesaurus V="El-Tor-Cholera"/>
              <diagnosethesaurus V="Choleraartige Dysenterie"/>
              <diagnosethesaurus V="El-Tor-Enteritis"/>
            </diagnosethesaurus_liste>
            <kodierrichtlinien_liste>
              <akr_ref V="B1101"/>
            </kodierrichtlinien_liste>
          </diagnose>
        </group>
      </kap>
    </h>
    Could you test that query on the same document to see if it's really a problem with your version?

    Tsuji's suggestion works as well, after removing the extra curly brackets as mentioned.

    I also tested with a fictitious document containing multiple "kapitel" elements : no issue.

    Edited by: odie_63 on 18 sept. 2011 19:14
  • 9. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Thank you for your last response. Now I tried to execute your code (please note that names are somewhat different on my installation as those you were using are already in use, but the settings are the same). Here you have source code and execution results:
    SQL> @q
    SQL> drop table icd0
      2  /
    
    Tabelle wurde gelöscht.
    
    SQL> create table icd0 (
      2   id number,
      3   xml_document xmltype
      4  )
      5  xmltype xml_document store as securefile binary xml
      6  /
    
    Tabelle wurde erstellt.
    
    SQL>  insert into icd0 values(1, xmltype(bfilename('SOURCE_DIR', 'icd_abridged.xml'), nls_charset_id('WEISO8859P15')))
      2  /
    
    1 Zeile wurde erstellt.
    
    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 := $d/n1:ehd/n1:body/n2:icd_stammdaten/n2:kapitel_liste/n2:kapitel
      7        return element h
      8        {
      9          $d/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@EX,
     10          $d/n1:ehd/n1:header/n1:provider/n1:organization/n1:id/@RT,
     11          element kap
     12          {
     13            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:nummer,
     14            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bezeichnung,
     15            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:von_icd_code,
     16            $p[n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bis_icd_code,
     17            element group
     18            {
     19            $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bezeichnung,
     20            $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:von_icd_code,
     21            $p/n2:gruppen_liste/n2:gruppe[n2:diagnosen_liste/descendant::n2:diagnose/n2:icd_code/@V=$icd_code]/n2:bis_icd_code,
     22            $p/n2:gruppen_liste/n2:gruppe/n2:diagnosen_liste/descendant::n2:diagnose[n2:icd_code/@V=$icd_code]
     23            }
     24          }
     25        }'
     26        PASSING xml_document as "d", 'A00.1' as "icd_code"
     27        RETURNING CONTENT
     28    )
     29    AS CLOB INDENT SIZE = 2
     30  ) as result
     31  FROM icd
     32  /
    
    RESULT                                                                                                                  
    --------------------------------------------------------------------------------                                        
    <h EX="74" RT="1.2.276.0.76.5.233">                                                                                     
      <kap>                                                                                                                 
        <group/>                                                                                                            
      </kap>                                                                                                                
    </h>                                                                                                                    
                                                                                                                            
    
    1 Zeile wurde ausgewählt.
    
    SQL> spool off
    Note that when I say "select * from icd0" I get the proper contents with header and abridged body section.

    Meanwhile I also tried to biuld the norewrite-hint into the original version, and I worked! This is most important for me, whereas I am a bit uncertain why our machines behave so differently. Do you have an idea?

    Kind regards,
    Miklos
  • 10. Re: Syntax error when using "let"
    odie_63 Guru
    Currently Being Moderated
     RESULT                                                                                                                  
     --------------------------------------------------------------------------------                                        
     <h EX="74" RT="1.2.276.0.76.5.233">                                                                                     
       <kap>                                                                                                                 
         <group/>                                                                                                            
       </kap>                                                                                                                
     </h>                                                                                                                    
    OK, you get empty elements on 11.2.0.2, so there's definitely an issue on that version.
    The fact that the NO_XML_QUERY_REWRITE hint works means that it's a problem with the optimizer trying to use the internal binary XML structure to process the XQuery.

    I can't say much more than that, but since the test case is fairly simple, you should probably log an SR with Oracle Support on that one.
  • 11. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    HI odie,

    Yes, thank you. I will do that but first I have to set up the test case for both versions.

    Kind regards,
    Miklos
  • 12. Re: Syntax error when using "let"
    mh*379860*ly Newbie
    Currently Being Moderated
    Now it is simply working A1.

Legend

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