4 Replies Latest reply: Sep 13, 2012 8:00 AM by odie_63 RSS

    XML logic not working

    860250
      Hi,

      Below logic is not working as there are 2 names (John Doe & Dan Kim). I always want to pick the first name in xml script ie (John Doe). Can anyone help me on how to modify the below logic to pick only first occurance of name in xml script.

      DECLARE

      fir varchar2(2000):='First';
      las varchar2(2000):='Last';

      lx XMLTYPE := XMLTYPE('<dept>
           <employee>
                <name>
                     <first>John</first>
                     <last>Doe</last>
                </name>
      <name>
                     <first>Dan</first>
                     <last>Kim</last>
                </name>
           </employee>
           </dept>
      ');

      BEGIN

      dbms_output.put_line(fir);
      dbms_output.put_line(las);

      SELECT X.firstname, X.lastname
      INTO fir, las
      FROM
      XMLTABLE ('//dept/employee/name' passing lx
      COLUMNS
      firstname      VARCHAR(20)      PATH 'first',
      lastname      VARCHAR(25)      PATH 'last') AS X;

      dbms_output.put_line(fir);
      dbms_output.put_line(las);

      END;
        • 1. Re: XML logic not working
          odie_63
          You can do it with a positional predicate :
          SQL> DECLARE
            2  
            3  fir  varchar2(2000):='First';
            4  las  varchar2(2000):='Last';
            5  
            6  lx XMLTYPE := XMLTYPE('<dept>
            7       <employee>
            8            <name>
            9                 <first>John</first>
           10                 <last>Doe</last>
           11            </name>
           12    <name>
           13                 <first>Dan</first>
           14                 <last>Kim</last>
           15            </name>
           16       </employee>
           17       </dept>
           18  ');
           19  
           20  BEGIN
           21  
           22  SELECT X.firstname, X.lastname
           23  INTO fir, las
           24  FROM
           25  XMLTABLE ('/dept/employee/name[1]' passing lx
           26     COLUMNS
           27     firstname VARCHAR2(20)      PATH 'first',
           28     lastname      VARCHAR2(25)      PATH 'last') AS X;
           29  
           30   dbms_output.put_line(fir);
           31   dbms_output.put_line(las);
           32  
           33  END;
           34  /
           
          John
          Doe
           
          PL/SQL procedure successfully completed
           
          A few other tips :

          - Use VARCHAR2, not VARCHAR.
          - Don't use descendant axes (//) unless you have to.
          • 2. Re: XML logic not working
            860250
            This is just a example. In my scenario I need to use // as I don't know the exact node position in the xml script.

            Please let me know if there is a way to do that.
            • 3. Re: XML logic not working
              860250
              I have one more example. In the below I want to pick only the first node for AGE, how can I pick that right now I am trying to use like //age[1] but not working.

              DECLARE

              fir varchar2(2000):='First';
              las varchar2(2000):='Last';

              lx XMLTYPE := XMLTYPE('<dept>
              <employee>
              <name>
              <first>John</first>
              <last>Doe</last>
              </name>
              <name>
              <first>Dan</first>
              <last>Kim</last>
              </name>
              </employee>
              <age>23</age>
              <name>
              <first>Dan</first>
              <last>Kim</last>
              </name>
              <age>25</age>
              </dept>
              ');

              BEGIN

              dbms_output.put_line(fir);
              dbms_output.put_line(las);

              SELECT X.firstname, X.lastname
              INTO fir, las
              FROM
              XMLTABLE ('//dept/employee/name' passing lx
              COLUMNS
              firstname VARCHAR(20) PATH 'first',
              lastname VARCHAR(25) PATH 'last') AS X;
              dbms_output.put_line(fir);
              dbms_output.put_line(las);

              END;
              • 4. Re: XML logic not working
                odie_63
                The following works for me. What are you doing differently?
                SQL> set serveroutput on
                SQL> 
                SQL> DECLARE
                  2  
                  3  v_age number;
                  4  
                  5  lx XMLTYPE := XMLTYPE('<dept>
                  6  <employee>
                  7  <name>
                  8  <first>John</first>
                  9  <last>Doe</last>
                 10  </name>
                 11  <name>
                 12  <first>Dan</first>
                 13  <last>Kim</last>
                 14  </name>
                 15  </employee>
                 16  <age>23</age>
                 17  <name>
                 18  <first>Dan</first>
                 19  <last>Kim</last>
                 20  </name>
                 21  <age>25</age>
                 22  </dept>
                 23  ');
                 24  
                 25  BEGIN
                 26  
                 27  
                 28  SELECT x.age
                 29  INTO v_age
                 30  FROM
                 31  XMLTABLE ('//age[1]' passing lx
                 32  COLUMNS age number path '.' ) AS X;
                 33  
                 34  dbms_output.put_line(v_age);
                 35  
                 36  END;
                 37  /
                 
                23
                 
                PL/SQL procedure successfully completed