Extract HTML content from XML in Oracle

Question

I am migrating an application from Sedna to Oracle. I have a collection/table that contents HTML code.

<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>

My goal is to extract the HTML content of <description> tag. When I run the following query, it returns the content but it removes the HTML tags.

SELECT xmllines.* FROM
XMLTABLE ( '/descriptions'
    PASSING xmltype('<descriptions> 
<description acad-plan="SAGSY1">
<p>
  <strong>test?</strong>
</p>
<p>test</p>
</description>
</descriptions>'
) COLUMNS
        id VARCHAR2(512) PATH 'description'
) xmllines;

The output of this query is :

test?test

What I am looking for is

<p><strong>test </strong> </p> <p>test</p>

Any idea how to fix this?

When you write which query? Also please include the the original value, the result you get now, and what you want to end up with. — Alex Poole
– Alex Poole, Commented Feb 27, 2018 at 16:25
Thanks for your feedback. I want to extract the content of <description> tag as is. The query I am using is: select * from xmltype(bfilename('PTOOL_TMP', 'program-details.xml'), nls_charset_id('UTF-8') ) columns html varchar2(1025) path '/descriptions/description' Thanks — tjxob
– tjxob, Commented Feb 27, 2018 at 16:28
Please edit your question to include your code and other information. — Alex Poole
– Alex Poole, Commented Feb 27, 2018 at 16:31

Alex Poole · Accepted Answer · 2018-02-27 17:24:47Z

You appear to want one of these:

select html
from xmltable('/'
  passing xmltype('<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>')
  columns html xmltype path '/descriptions/description/*');

select html
from xmltable('/descriptions'
  passing xmltype('<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>')
  columns html xmltype path 'description/*'
);

select html
from xmltable('/descriptions/description'
  passing xmltype('<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>')
  columns html xmltype path '*'
);

depending on the number and type of repeated nodes; if any; or if there is only one node to extract you coudl use an XMLQuery instead:

select xmlquery('/descriptions/description/*'
  passing xmltype('<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>')
  returning content) as html
from dual;

All of those get the same output:

HTML                                                                            
--------------------------------------------------------------------------------
<p><strong>test?</strong></p><p>test</p>

I've changed the path to get * under the description node; and changed the returned column type from the XMLTable calls to XMLType. The XMLQuery returns that type too.

If you want the result as a plain string you can use the XMLType getStringVal() function; like this for the XMLTable versions:

select xmltype.getStringVal(html) as html
from xmltable(...)

or like this for the XMLQuery version:

select xmlquery('/descriptions/description/*'
  passing xmltype('<descriptions>
  <description acad-plan="SAGSY1">
    <p>
      <strong>test?</strong>
    </p>
    <p>test</p>
  </description>
</descriptions>')
  returning content).getStringVal() as html
from dual;

Thanks Alex for the response. It works perfectly. And thanks for your patience. Have a good day!

Collectives™ on Stack Overflow

Extract HTML content from XML in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related