5 Replies Latest reply: Apr 23, 2013 5:32 AM by Martina1018 RSS

    Passing PL/SQL varchar2 variable to XML Query

    Martina1018
      Hi guys,

      i'm having trouble passing a pl/sql varchar2 variable to an XMLQuery.
      This is the relevant part of my code:
      DECLARE
         lo_result   XMLTYPE;         --contains the XML which is being parsed below
         lo_return   XMLTYPE;             -- for the XML result returned by XMLQuery
         lo_start    VARCHAR2 (100) DEFAULT 'Toronto'; -- a PL/SQL varchar2 variable
         lo_end      VARCHAR2 (100) DEFAULT 'Ottawa';  -- a PL/SQL varchar2 variable
      BEGIN
         SELECT XMLQUERY (
                   'for $i in //leg[start_address[text()[contains(.,$lo_start)]] and end_address[text()[contains(.,$lo_end)]]]/distance/value/text() return ($i)'
                   PASSING lo_result, lo_start AS "lo_start", lo_end AS "lo_end"
                   RETURNING CONTENT)
           INTO lo_return
           FROM DUAL;
      END;
      The XPath expression is correct but it doesn't seem to accept my variables since lo_return is empty.
      I think the variables should be of type Xmltype but the compiler won't let me convert them because they do not contain any XML tags .
      Hope anyone can help.

      Thanks,

      Martina
        • 1. Re: Passing PL/SQL varchar2 variable to XML Query
          odie_63
          Hi Martina,

          Difficult to understand what's wrong without having at least :

          - a sample input XML
          - a database version (SELECT * FROM v$version)
          - error message (if any)

          On 10.2, non-XMLType variables have to be passed wrapped in an XMLType container.
          This restriction has been removed on 11g.

          So if you're on 10.2, you can try this :
          PASSING lo_result
                , sys_xmlgen(lo_start) AS "lo_start"
                , sys_xmlgen(lo_end) AS "lo_end"
          If you're on 11g, please give the exact version and post a sample XML.
          • 2. Re: Passing PL/SQL varchar2 variable to XML Query
            Martina1018
            Hi,

            thanks for your answer.
            The database version I'm using is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.
            A (very shortened) sample input XML would be:
            <?xml version="1.0" encoding="UTF-8"?>
            
            <DirectionsResponse>
              <status>OK</status>
              <route>
                <summary>I-75 N</summary>
                <leg>
                  <duration>
                    <value>48220</value>
                    <text>13 Stunden, 24 Minuten</text>
                  </duration>
                  <distance>
                    <value>1404935</value>
                    <text>1.405 km</text>
                  </distance>
                  <start_location>
                    <lat>43.6533100</lat>
                    <lng>-79.3827700</lng>
                  </start_location>
                  <end_location>
                    <lat>34.7303300</lat>
                    <lng>-86.5860700</lng>
                  </end_location>
                  <start_address>Toronto, Ontario, Kanada</start_address>
                  <end_address>Huntsville, Alabama, Vereinigte Staaten</end_address>
                </leg>
              </route>
            </DirectionsResponse>
            As you can probably see, I'm trying to query a Google Distance API result to get (in this sample case) the distance from Toronto to Huntsville.
            The compiler does not produce any error message, it just seems to ignore the part with the XMLQuery.

            Thanks,

            Martina
            • 3. Re: Passing PL/SQL varchar2 variable to XML Query
              AlexAnd
              may be i missed anything but
              >
              <start_address>Toronto, Ontario, Kanada</start_address>
              <end_address>Huntsville, Alabama, Vereinigte Staaten</end_address>
              >
              and
              >
              lo_start VARCHAR2 (100) DEFAULT 'Toronto'; -- a PL/SQL varchar2 variable
              lo_end VARCHAR2 (100) DEFAULT 'Ottawa'; -- a PL/SQL varchar2 variable
              >
              so
              SQL> SELECT XMLQUERY (
                2              'for $i in //leg[start_address[text()[contains(.,$lo_start)]] and end_address[text()[contains(.,$lo_end)]]]/distance/value/text() return ($i)'
                3               PASSING
                4               xmltype('<DirectionsResponse>
                5    <status>OK</status>
                6    <route>
                7      <summary>I-75 N</summary>
                8      <leg>
                9        <duration>
               10          <value>48220</value>
               11          <text>13 Stunden, 24 Minuten</text>
               12        </duration>
               13        <distance>
               14          <value>1404935</value>
               15          <text>1.405 km</text>
               16        </distance>
               17        <start_location>
               18          <lat>43.6533100</lat>
               19          <lng>-79.3827700</lng>
               20        </start_location>
               21        <end_location>
               22          <lat>34.7303300</lat>
               23          <lng>-86.5860700</lng>
               24        </end_location>
               25        <start_address>Toronto, Ontario, Kanada</start_address>
               26        <end_address>Huntsville, Alabama, Vereinigte Staaten</end_address>
               27      </leg>
               28    </route>
               29  </DirectionsResponse>'),
               30               'Toronto' AS "lo_start", 'Ottawa' AS "lo_end"
               31               RETURNING CONTENT)
               32       FROM DUAL;
               
              XMLQUERY('FOR$IIN//LEG[START_A
              --------------------------------------------------------------------------------
               
              SQL> 
              SQL> 
              SQL> 
              SQL> SELECT XMLQUERY (
                2              'for $i in //leg[start_address[text()[contains(.,$lo_start)]] and end_address[text()[contains(.,$lo_end)]]]/distance/value/text() return ($i)'
                3               PASSING
                4               xmltype('<DirectionsResponse>
                5    <status>OK</status>
                6    <route>
                7      <summary>I-75 N</summary>
                8      <leg>
                9        <duration>
               10          <value>48220</value>
               11          <text>13 Stunden, 24 Minuten</text>
               12        </duration>
               13        <distance>
               14          <value>1404935</value>
               15          <text>1.405 km</text>
               16        </distance>
               17        <start_location>
               18          <lat>43.6533100</lat>
               19          <lng>-79.3827700</lng>
               20        </start_location>
               21        <end_location>
               22          <lat>34.7303300</lat>
               23          <lng>-86.5860700</lng>
               24        </end_location>
               25        <start_address>Toronto, Ontario, Kanada</start_address>
               26        <end_address>Huntsville, Alabama, Vereinigte Staaten</end_address>
               27      </leg>
               28    </route>
               29  </DirectionsResponse>'),
               30               'Toronto' AS "lo_start", /*'Ottawa'*/'Huntsville' AS "lo_end"
               31               RETURNING CONTENT)
               32       FROM DUAL;
               
              XMLQUERY('FOR$IIN//LEG[START_A
              --------------------------------------------------------------------------------
              1404935
               
              SQL> 
              • 4. Re: Passing PL/SQL varchar2 variable to XML Query
                odie_63
                Alex is right, <end_address> doesn't contain "Ottawa" so the query returns an empty sequence.


                A few other tips :

                - use NO_XML_QUERY_REWRITE hint when doing XQuery from PL/SQL variables. It'll instruct the optimizer to not even try to make optimization since none is possible.

                - for readability, you can use an extended FLWOR expression (see example below)

                - Use XMLCast function when the result is a scalar datatype. If the result is a sequence of mutiple logical "records", use XMLTable.

                - Whenever possible, always write full paths, avoid descendant axis.
                SQL> DECLARE
                  2     lo_result   XMLTYPE :=
                  3    xmltype('<?xml version="1.0" encoding="UTF-8"?>
                  4  <DirectionsResponse>
                  5    <status>OK</status>
                  6    <route>
                  7      <summary>I-75 N</summary>
                  8      <leg>
                  9        <duration>
                 10          <value>48220</value>
                 11          <text>13 Stunden, 24 Minuten</text>
                 12        </duration>
                 13        <distance>
                 14          <value>1404935</value>
                 15          <text>1.405 km</text>
                 16        </distance>
                 17        <start_location>
                 18          <lat>43.6533100</lat>
                 19          <lng>-79.3827700</lng>
                 20        </start_location>
                 21        <end_location>
                 22          <lat>34.7303300</lat>
                 23          <lng>-86.5860700</lng>
                 24        </end_location>
                 25        <start_address>Toronto, Ontario, Kanada</start_address>
                 26        <end_address>Huntsville, Alabama, Vereinigte Staaten</end_address>
                 27      </leg>
                 28    </route>
                 29  </DirectionsResponse>') ;
                 30  
                 31     lo_return   NUMBER;
                 32     lo_start    VARCHAR2 (100) DEFAULT 'Toronto';
                 33     lo_end      VARCHAR2 (100) DEFAULT 'Alabama';
                 34  
                 35  BEGIN
                 36  
                 37     SELECT /*+ no_xml_query_rewrite */
                 38            XMLCast(
                 39              XMLQUERY (
                 40                'for $leg in /DirectionsResponse/route/leg
                 41                 where contains($leg/start_address, $lo_start)
                 42                   and contains($leg/end_address, $lo_end)
                 43                 return $leg/distance/value'
                 44                PASSING lo_result
                 45                      , lo_start AS "lo_start"
                 46                      , lo_end AS "lo_end"
                 47                RETURNING CONTENT
                 48              )
                 49              as number
                 50            )
                 51     INTO lo_return
                 52     FROM DUAL;
                 53  
                 54     dbms_output.put_line('Distance = ' || lo_return);
                 55  
                 56  END;
                 57  /
                 
                Distance = 1404935
                 
                PL/SQL procedure successfully completed
                 
                Edited by: odie_63 on 23 avr. 2013 11:48
                • 5. Re: Passing PL/SQL varchar2 variable to XML Query
                  Martina1018
                  Sorry AlexAnd, my mistake.
                  As I said I shortened the xml, the original xml does contain Ottawa as an end_address. So that was not the problem.

                  However, I tried the code of odie_63 which in fact did return a distance.
                  I thought there is something wrong with my XMLQuery (I was not sure how to pass the two string variables, that's why I started the thread)
                  but the problem seems to be in my request to Google. But I'm sure I'll figure it out myself.
                  Anyways, thanks for your time. I really appreciate your help and you guys do a great job in the forum!

                  Martina