1 Reply Latest reply on Nov 21, 2012 10:58 AM by BluShadow

    BLOB column datatype

    user12057782
      Hi

      I have one column which is of datatype BLOB.File was there in the blob column. File Contains the XML tags.My Requirement is need to extract the tag value.
      EX:in XLM i need to extract <name>Rajesh</name>

      Bottom line is Need to extract one tag value from BLOB dataype column which contains the file in which XML tags were there.

      Plz Help
        • 1. Re: BLOB column datatype
          BluShadow
          user12057782 wrote:
          Hi

          I have one column which is of datatype BLOB.File was there in the blob column. File Contains the XML tags.My Requirement is need to extract the tag value.
          EX:in XLM i need to extract <name>Rajesh</name>
          That's not good. BLOB should store binary data. CLOB should store character based data. XML is character based, so you could end up corrupting the XML by storing it in a BLOB.

          Oracle also has an XMLTYPE datatype specifically for storing XML data in, so why aren't you using that?
          Bottom line is Need to extract one tag value from BLOB dataype column which contains the file in which XML tags were there.
          Here's a basic example of extracting XML from an XMLTYPE (you can easily convert CLOB to XMLTYPE using it's constructor method)...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select XMLTYPE('<cube>
            2                                <dimension>
            3                                   <sourcename>A1</sourcename>
            4                                   <targetname>X1</targetname>
            5                                </dimension>
            6                                <dimension>
            7                                   <sourcename>B1</sourcename>
            8                                   <targetname>Y1</targetname>
            9                                </dimension>
           10                                <dimension>
           11                                   <sourcename>C1</sourcename>
           12                                   <targetname>Z1</targetname>
           13                                </dimension>
           14                             </cube>
           15                           ') as xml
           16             from dual
           17            )
           18  -- end of test data
           19  select x.*
           20  from t
           21      ,XMLTABLE('/cube/dimension'
           22                PASSING t.xml
           23                COLUMNS sourcename VARCHAR2(2) PATH '/dimension/sourcename'
           24*                      ,targetname VARCHAR2(2) PATH '/dimension/targetname') x
          SQL> /
          
          SO TA
          -- --
          A1 X1
          B1 Y1
          C1 Z1
          
          SQL>
          If that isn't quite what you want, provide more information with your database version, example data and expected output, as detailed in the FAQ: {message:id=9360002}
          I'm surprised that as a member who's been here for 3 years+ and asked 32 questions, you still haven't understood how to ask a question so that people can help you. Perhaps that explains why you still have 27 question showing as unanswered.