Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: Reading XML data using PL SQL

    I have a table in which one column is long data type which has data stored in XML format
    How can I get the value of node=buyer from it?
    As in
    XML file
    <otherfields>
    <buyer>
    Pepsi
    </buyer>
    </otherfilelds>

    Oracle version 9i

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    51
    I tried
    create or replace function longsubstr123( p_rowid in rowid,
    p_from in number,
    p_for in number )
    return varchar2
    as
    l_tmp long;
    begin
    select payload into l_tmp from Ts where rowid = p_rowid;

    return substr( l_tmp , p_from, p_for );
    end;

    What I want to do is that variable l_tmp should get the value of Pepsi in it
    Can I parse the variable in PL SQL?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this? First, create a test table and insert some data into it:
    Code:
    SQL> CREATE TABLE TEST (xml LONG);
    
    Table created.
    
    SQL>
    SQL> INSERT INTO TEST VALUES ('
      2  <otherfields>
      3  <buyer>
      4  Coca Cola
      5  </buyer>
      6  </otherfilelds>');
    
    1 row created.
    
    SQL> INSERT INTO TEST VALUES ('
      2  <otherfields>
      3  <buyer>
      4  Pepsi
      5  </buyer>
      6  </otherfilelds>');
    
    1 row created.
    
    SQL>
    Now create a function which will return string from a long:
    Code:
    SQL> CREATE OR REPLACE FUNCTION fun_l2s (p_rowid IN ROWID)
      2  RETURN VARCHAR2
      3  IS
      4    l_xml TEST.xml%TYPE;
      5  BEGIN
      6    SELECT xml INTO l_xml
      7      FROM TEST
      8     WHERE ROWID = p_rowid;
      9
     10    RETURN l_xml;
     11  END;
     12  /
    
    Function created.
    
    SQL>
    Finally, do some calculation to find beginning and the end of a string we are interested in (it is between buyer tags) - we'll use INSTR and SUBSTR functions:
    Code:
    SQL> SELECT
      2    SUBSTR(fun_l2s(ROWID),
      3           INSTR(fun_l2s (ROWID), '<buyer>', 1) + 7,
      4           INSTR(fun_l2s (ROWID), '</buyer>', 1) -
      5                 INSTR(fun_l2s (ROWID), '<buyer>', 1) - 7
      6          ) product
      7  FROM TEST;
    
    PRODUCT
    ---------------------------------------------------------------
    Coca Cola
    Pepsi
    
    SQL>
    (I removed extra lines to improve the output). Now, this code might need to be adjusted, but - generally speaking - might lead to a solution.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oracle's ExtractValue() function might be useful.

    Alan

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by AlanP
    Oracle's ExtractValue() function might be useful.

    Alan
    Something like this (a combination of EXTRACT and EXTRACTVALUE) :

    Code:
    rbaraer@Ora10g> select extract(XMLType('<otherfields><buyer>Pepsi</buyer></otherfields>'),'//otherfields/buyer') Buyer from dual;
    
    BUYER
    -----------------------------------------------------------------------------------------------------------------------------------
    <buyer>Pepsi</buyer>
    
    rbaraer@Ora10g> select extractvalue(extract(XMLType('<otherfields><buyer>Pepsi</buyer></otherfields>'),'//otherfields/buyer'),'*') Buyer from dual;
    
    BUYER
    -----------------------------------------------------------------------------------------------------------------------------------
    Pepsi
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Good stuff. Didn't knew the existence of XMLType to typecast to XML.

Posting Permissions

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