How to Extract xmltype as xml in Oracle

Question

I am trying to extract xml from the xmltype doc.

DECLARE
  xmlData         XMLType;
  sDocumentId     VARCHAR2(100);
  sFormat         VARCHAR2(100);
  cData           CLOB;
BEGIN
  xmlData := XMLType( '<main>
                      <document_id>1234567</document_id>
                      <format>E66</format>
                      <data><Fname>ABCD</Fname><Lname>EFGD</Lname></data>
                  </main>');

  SELECT ExtractValue(xmlData, '/main/document_id/text()'),
         ExtractValue(xmlData, '/main/format/text()'),
         ExtractValue(xmlData, '/main/data/text()')
  INTO sDocumentId,
       sFormat,
       cData
  FROM (SELECT xmlData  FROM DUAL);

  dbms_output.put_line(sDocumentId);
  dbms_output.put_line(sFormat);
  dbms_output.put_line(cData);
END;

How to get the output like this:

DocumentId = 1234567
sFormat = E66
cData = <Fname>ABCD</Fname><Lname>EFGD</Lname>

xmlData.Extract('/main/data/text()').getStringVal() gets you <data> <Fname>ABCD</Fname> <Lname>EFGD</Lname> </data> close, but not close enough ;) — booyaa
– booyaa, Commented Oct 15, 2015 at 13:57
came up with which did get xml fragment: Extract(xmlData, '/main/data/node()').getStringVal() — booyaa
– booyaa, Commented Oct 15, 2015 at 15:10

Wernfried Domscheit · Accepted Answer · 2015-10-15 13:54:24Z

2

You can do it like this:

SELECT  EXTRACTVALUE(xmlData, '/main/document_id/text()'),
    EXTRACTVALUE(xmlData, '/main/format/text()'),
    EXTRACT(xmlData, '/main/data').GetClobVal()
INTO   sDocumentId,
    sFormat,
    cData
FROM (SELECT  xmlData  FROM DUAL) t;

Please note, EXTRACTVALUE and EXTRACT are deprecated. However, I did not manage to use XMLTABLE, but this could be a starting point:

SELECT *
INTO   sDocumentId,
    sFormat,
    cData
FROM XMLTABLE('/main' PASSING xmlData COLUMNS 
    sDocumentId  VARCHAR2(100) PATH 'document_id/text()',
    sFormat  VARCHAR2(100) PATH 'format/text()',
    cData  VARCHAR2(1000) PATH 'data/text()');

edited Oct 15, 2015 at 13:54

answered Oct 15, 2015 at 13:43

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Prateik Over a year ago

In this, how can we get the clob data for 'data'? As I wanted to to use something live GetClobVal() to get it.

Demodave Over a year ago

Can you please provide full query Including variables?

Chaitanya Kotha · Accepted Answer · 2015-10-15 13:41:09Z

2

Use the below code.

DECLARE
xmlData         XMLType;
sDocumentId     VARCHAR2(100);
sFormat         VARCHAR2(100);
cData           Clob;

BEGIN

xmlData := XMLType( '<main>
                <document_id>1234567</document_id>
                <format>E66</format>
                <data><Fname>ABCD</Fname><Lname>EFGD</Lname></data>
            </main>');


SELECT  ExtractValue(xmlData, '/main/document_id/text()'),
        ExtractValue(xmlData, '/main/format/text()'),
        EXTRACT(xmlData,'/main/data').getClobVal()
INTO   sDocumentId,
       sFormat,
       cData
FROM ( SELECT xmlData  FROM DUAL);


dbms_output.put_line(sDocumentId);
dbms_output.put_line(sFormat);
dbms_output.put_line(cData);

END;

Output will be

1234567
E66
<data><Fname>ABCD</Fname><Lname>EFGD</Lname></data>

answered Oct 15, 2015 at 13:41

Chaitanya Kotha

4762 silver badges4 bronze badges

2 Comments

Chaitanya Kotha Over a year ago

Use EXTRACT function to extract xml value.And getClobVal() to get the value to clob variable.

Demodave Over a year ago

How do you get this to display? Mine only says PL/SQL procedure successfully completed.

Collectives™ on Stack Overflow

How to Extract xmltype as xml in Oracle

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related