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>

Answer 1

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>