4 Replies Latest reply: Dec 6, 2012 6:45 AM by RajeshKanna RSS

    Read the xmldata from table

    RajeshKanna
      Hi,

      I have created the following table contains only one column

      create table book_inf (booksinf clob);

      the booksinf column contains the following xmldata
      <BOOKS>
        <BOOK-ID> I1001 </BOOK-ID>
        <BOOK-NAME> SQL </BOOK-NAME>
      </BOOKS>
      how to read the values book-id and book-name one by one by using DBMS_LOB.

      can any one help me..?
        • 1. Re: Read the xmldata from table
          Stew Ashton
          Why do you want to use DBMS_LOB?

          The best way to do this depends on your Oracle version. What is it?
          • 2. Re: Read the xmldata from table
            RajeshKanna
            I am using 11g enterprise edition
            • 3. Re: Read the xmldata from table
              odie_63
              In addition, if you're at design time, use an XMLType column to store XML, it'll save you some troubles later.
              • 4. Re: Read the xmldata from table
                BluShadow
                You would be better using an XMLTYPE column to store your XML data.
                SQL> create table book_inf(booksinf XMLTYPE);
                
                Table created.
                
                SQL> ed
                Wrote file afiedt.buf
                
                  1* insert into book_inf(booksinf) values (XMLTYPE('<BOOKS><BOOK-ID>I1001</BOOK-ID><BOOK-NAME>SQL</BOOK-NAME></BOOKS>'))
                SQL> /
                
                1 row created.
                
                SQL> ed
                Wrote file afiedt.buf
                
                  1  select x.*
                  2  from   book_inf
                  3        ,xmltable('/BOOKS'
                  4                  passing book_inf.booksinf
                  5                  columns book_id   varchar2(20) path './BOOK-ID'
                  6                         ,book_name varchar2(20) path './BOOK-NAME'
                  7*                ) x
                SQL> /
                
                BOOK_ID              BOOK_NAME
                -------------------- --------------------
                I1001                SQL