Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: how to store xml in blob from a query.

    Dear all

    i want to store xml in database. i have following questions,
    1) in which col should i keep xml .
    2) right now i am keeping it in blob columns, so please tel me how can i insert update a record in blob col from query, which can be run from worksheet.

    I am using oracle 11g express edition.

    yours sincerely

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >1) in which col should i keep xml .

    Data Types

    >2) right now i am keeping it in blob columns, so please tel me how can i insert update a record in blob col from query, which can be run from worksheet.

    You don't/can't
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    1) Use XmlType datatype. Using this datatype will ensure that values in the column are in fact valid XML documents. Just like you would use a DATE datatype for dates and NUMBER datatype for numbers, you should use XmlType for XML.
    Code:
    dayneo@RMSD> create table XMLTEST_TBL (myxml_col xmltype);
    
    Table created.
    2) If you are storing your XML in a BLOB column, then you will need to convert it to a CLOB first and then parse it. You can parse and manipulate the XML using a combination of XmlType, DBMS_XMLDOM, DBMS_XMLPARSER and DBMS_XSLPROCESSOR. I assume you know how to get it back to BLOB from CLOB since you are already storing the XML as a BLOB. If not, post another question.

    Blob to clob function:
    Code:
    create or replace function blob2clob(p_blob in blob) return clob as
    
      l_result clob;
      l_buf    varchar2(32767);
      l_pos    pls_integer;
      l_bufsz  pls_integer;
      l_chunks pls_integer;
    
    begin
    
      dbms_lob.createtemporary(l_result, true);
      l_pos   := 1;
      l_bufsz := 32767;
      l_chunks:= ceil(dbms_lob.getlength(p_blob) / l_bufsz);
      for i in 1..l_chunks loop
    
        l_buf := utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, l_buf, l_pos));
        dbms_lob.writeappend(l_result, length(l_buf), l_buf);
        l_pos := l_pos + l_bufsz;
    
      end loop;
    
    	return l_result;
    
    end blob2clob;
    /

Posting Permissions

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