1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

XML query help - Why are attributes are not getting compared?

Discussion in 'SQL PL/SQL' started by nidhichutani, May 25, 2009.

  1. nidhichutani

    nidhichutani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Question1) I have a simple XML DB example here, can anyone suggest what am I missing?

    I am trying to retrieve names of all Managers whose salary is less than 800 or is listed with a star "*".
    The below query outputs all employees
    A - 1000
    B - *
    C - 500
    D - $

    Even ones with salary 1000 and $ are being thrown in the output.

    Thanks
    -Nick189
    ======================================================
    Code (SQL):

    DROP INDEX emp_idx;
    DROP TABLE emp;
    CREATE TABLE emp OF XMLTYPE;
    INSERT INTO emp
         VALUES (XMLTYPE
                    ('<emps>
    <title>Manager</title>
    <info name="A" sal="1000"/>
    <info name="B" sal="*"/>
    <info name="C" sal="500"/>
    <info name="D" sal="$"/>
    </emps>'

                    ));

    CREATE INDEX emp_idx ON emp(object_value) INDEXTYPE IS ctxsys.CONTEXT;


    SELECT EXTRACTVALUE (VALUE (t), '//info/@name') emp_name,
           EXTRACTVALUE (VALUE (t), '//info/@sal') salary
      FROM emp r, TABLE (XMLSEQUENCE (EXTRACT (object_value, '//info'))) t
     WHERE     EXISTSNODE (object_value, '/emps[title="Manager"]') = 1
           AND EXISTSNODE (object_value, '/emps/info[@sal<"800"]') = 1
        OR EXISTSNODE (object_value, '/emps/info[@sal="*"]') = 1;
     
    Question2) Also, Any suggestions on how to sum all salaries < 800? Can anyone give me a start on that?

    ================================================================
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hmmm i have limited experience with XMLTYPE but in your table the values are all characters aren't they? then how will the condition find values less than the characters "800". Shouldn't that be a number?

    Code (SQL):

    AND EXISTSNODE (object_value, '/emps/info[@sal<"800"]') = 1