Home » Developer & Programmer » JDeveloper, Java & XML » Extract Data from Clob field using Regular expression (Oracle 10g)
Extract Data from Clob field using Regular expression [message #525904] Thu, 06 October 2011 13:39 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I want to extract the data from the Clob field. I have the following table,

create table test123(col1 char(24), col2 clob);


And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
    <Attribute DataType="Text-40" DisplayName="DropDirectory"
        IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
    <Attribute DataType="Text-40" DisplayName="PrinterAlias"
        IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\\Printer3\Printer4"/>
    <Attribute DataType="Text-40" DisplayName="PrintServerHostName"
        IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
    <Attribute DataType="Count" DisplayName="PrintServerPort"
        IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');

Insert into test123 (col1,col2) values ('XYZ','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
    <Attribute DisplayName="PrinterAlias" Name="PrinterAlias" Value=" Printer1 Printer2  "/>
    <Attribute DataType="Text-40" DisplayName="DropDirectory"
        Name="DropDirectory" Value="Alternate_7.5/"/>
    <Attribute DataType="Text-40" DisplayName="PrintServerHostName"
        IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
    <Attribute DataType="Count" DisplayName="PrintServerPort"
        IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>
');


I need the output as,

Col1    Col2
ABCDE   \\Printer3\Printer4
XYZ     Printer1 Printer2


The data will be available like this only, The same XML tag is used everywhere.

Please help me to resolve..

Regards,
Ashoka
Re: Extract Data from Clob field using Regular expression [message #525907 is a reply to message #525904] Thu, 06 October 2011 14:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select col1,
  2         extractvalue(value(x), '/Attribute/@Value') col2
  3  from test123, table(xmlsequence(extract(xmltype(col2), '/Attributes/Attribute'))) x
  4  where extractvalue(value(x), '/Attribute/@Name') = 'PrinterAlias'
  5  /
COL1  COL2
----- ------------------------------
ABCDE \\Printer3\Printer4
XYZ    Printer1 Printer2

2 rows selected.

Regards
Michel
Previous Topic: read value from xml
Next Topic: Process XML data passed in parameter
Goto Forum:
  


Current Time: Fri Mar 29 01:21:12 CDT 2024