3

I'm trying to save a CLOB into a variable to perform operations like extract and such. I have this code:

DECLARE
  clob_rec CLOB;
  n_rec NUMBER:=100;
BEGIN
  SELECT LOB INTO clob_rec FROM table1 WHERE ID = 1234;
  n_rec := clob_rec.EXTRACT('//XPTO/text()', 'xmlns:XPTO').getNumVal();
END;

I want to save multiple values from the XML to various variables like n_rec. How can get an "instance of the object (CLOB)" to perform functions or methods like extract()?

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
DaveQuinn
  • 189
  • 1
  • 6
  • 19
  • 1
    You can use the [`dbms_lob`](http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_lob.htm) package to manipulate CLOB variables; but `extract` is an XML concept, so do you have an `XMLType` column, or do you need something like [`XMLCast`](http://docs.oracle.com/cd/E14072_01/appdev.112/e10492/xdb04cre.htm#CHDBIIJG)? Not sure I understand the issue enough to post this as an answer... – Alex Poole Sep 17 '12 at 17:19

1 Answers1

5

You need to convert it to an XMLtype first:

DECLARE
  clob_rec CLOB;
  n_rec NUMBER:=100;
  x XMLType;
BEGIN
  SELECT LOB INTO clob_rec FROM table1 WHERE ID = 1234;
  x := XMLType(clob_rec);
  n_rec := x.EXTRACT('//XPTO/text()', 'xmlns:XPTO').getNumVal();
END;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Thtat's exactly it Jeffrey Kemp. I would mark it as the solution but i have another question i would really appreciate your, or anyone elses's, help for. Is there any way i can ignore the namespaces when extracting the node values from the xml? All the XML validates against the same SCHEMA but they have different namespaces, some "er:xxx", some "ns:yyy". I would like to ignore the namespaces so i would have a way to aplly the same procedure to all my CLOBS. Thank you very much. – DaveQuinn Sep 18 '12 at 09:34
  • 1
    I think it's better to have separate procedures for different schemas; although I think you can skip the namespace by using the `*` xpath expression, e.g. `x.Extract('/*/node/text()')` - but I'm not a world expert on XPath so try it for yourself :) – Jeffrey Kemp Sep 19 '12 at 05:12