Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Unanswered: Extract attributes value from XML text

    Hello all!
    I need help in solving the following problem. I have a table which contains, in several fields, XML text, like this one
    <CH cl_id="9299944225636394922"><CL List="7 5 2 3 " Type="1" Action="1" Time="2007-05-04+01:50:09.148869"/><CL List="" Type="2" Action="1" Time="2007-05-04+01:50:10.224632"/></CH>

    How can I extract the value of each attribute, for example:if I want to know the value of cl_id, of type, etc, for each CL line, etc.

    Thank you very much!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not familiar with XML and I wouldn't know if there are built-in Oracle functions which would allow you to easily extract information you need, so I can offer only a user-defined function. Parameters would be the "attribute" you'd like to get and its "appearance" (as there are, for example, 2 'Time' attributes there).

    First, a test case:
    Code:
    CREATE TABLE TEST (col VARCHAR2(300));
    
    INSERT INTO TEST VALUES ('<CH cl_id="9299944225636394922"><CL List="7 5 2 3 " Type="1" Action="1" Time="2007-05-04+01:50:09.148869"/><CL List="" Type="2" Action="1" Time="2007-05-04+01:50:10.224632"/></CH>');
    A function:
    Code:
    CREATE OR REPLACE FUNCTION Ret_Attr (par_attr IN CHAR, par_app IN NUMBER)
    RETURN CHAR
    IS
      retval VARCHAR2(200);
    BEGIN
    SELECT REPLACE(
           SUBSTR(col, 
                  INSTR(col, par_attr, 1, par_app) + LENGTH(par_attr) + 2,
                  INSTR(SUBSTR(col, INSTR(col, par_attr, 1, par_app) + LENGTH(par_attr) + 2), '"')
                 ),
           '"', '')
      INTO retval												 
      FROM TEST;
      RETURN (retval);
    END;
    /
    Finally, the result:
    Code:
    SQL> select ret_attr('Time', 1) result from test;
    
    RESULT
    ----------------------------------------------------------
    
    2007-05-04+01:50:09.148869
    
    SQL> select ret_attr('Time', 2) result from test;
    
    RESULT
    ----------------------------------------------------------
    
    2007-05-04+01:50:10.224632
    
    SQL> select ret_attr('cl_id', 1) result from test;
    
    RESULT
    ----------------------------------------------------------
    
    9299944225636394922
    
    SQL>
    Write a loop if you'd like to fetch all known attributes and all their appearances. Also, I didn't take care about non-existing appearances (for example, combination of 'cl_id' + 3 or 'Time' + 4); you'd always get 'l_id =' as a result in such a case.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    This works on 10gR2
    Code:
    SQL>
    SQL> var xmlstring varchar2(1000)
    SQL>
    SQL> exec :xmlstring := '<CH cl_id="9299944225636394922"><CL List="7 5 2 3 " Type="1" Action="1" Time="2007-05-04+01:50:09.14
    8869"/><CL List="" Type="2" Action="1" Time="2007-05-04+01:50:10.224632"/></CH>';
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select cl_id, type
      2    from XMLTable
      3  (
      4     '/CH'
      5     passing XMLType(:xmlstring)
      6     columns
      7     cl_id varchar2(20) path '@cl_id',
      8     CL_XML XMLtype path 'CL'
      9  ) x,
     10  XMLTable
     11  (
     12     '/CL'
     13     passing x.CL_XML
     14     columns
     15     type varchar2(1) path '@Type'
     16  ) y
     17  /
    
    CL_ID                T
    -------------------- -
    9299944225636394922  1
    9299944225636394922  2
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •