Extract Data from Clob field using Regular expression [message #525904] |
Thu, 06 October 2011 13:39 |
|
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 |
|
Michel Cadot
Messages: 68693 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
|
|
|