Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Remove all HTML tags in a string

Go to solution
Former Member

on ‎2020 Oct 16 8:30 AM

0 Likes
5,577

Is there any good way to remove all HTML tags in a string with sql (preferably without function) that looks like this.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>
        </title>
        <style type="text/css">
            .csC583D0C8{text-align:left;text-indent:0pt;margin:12pt 0pt 12pt 0pt}
            .csCABEF5ED{color:#282832;background-color:transparent;font-family:Arial;font-size:10pt;font-weight:bold;font-style:normal;}
        </style>
    </head>
    <body>
        <p class="csC583D0C8"><span class="csCABEF5ED">Test row 1<br/>Test row2</span></p></body>
</html>

Results should be like this

Test row 1
Test row 2

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies
View Entire Topic
VolkerBarth
Contributor
‎2020 Oct 16 9:11 AM

AFAIK, as XHTML documents are using XML, you might be better off by trying the builtin OpenXML operator to access the desired document portions. - Note, while I have often used OpenXML for XML documents, I have not tried myself with XHTML.

Here's a starting sample:

create or replace variable varDoc xml;
set varDoc = 
'
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><title>
        </title>
        <style type="text/css">
            .csC583D0C8{text-align:left;text-indent:0pt;margin:12pt 0pt 12pt 0pt}
            .csCABEF5ED{color:#282832;background-color:transparent;font-family:Arial;font-size:10pt;font-weight:bold;font-style:normal;}
        </style>
    </head>
    <body>
        
Test row 1
Test row2
</body>
</html>';
select *
-- list all nodes
-- from OpenXML(varDoc, '//*')
-- filtered on the span node within the body's paragraphs
from OpenXML(varDoc, '//*:body/*:p/*:span')
   with (ID int '@mp:id',
         Node long varchar 'node()',
         Name long varchar '@mp:localname',
         Text long varchar 'text()',
         XmlText long varchar '@mp:xmltext',
         Class long varchar '@class')
order by ID;

This return the following row: 

ID;Node;Name;Text;XmlText;Class
606;'Test row 1';'span';'Test row 1';'Test row 1
Test row2';'csCABEF5ED'

Apparently, in contrast to normal XML documents where your data would be easily accessable via well-named attributes or elements, here the XML contents of the "span element" would need to be extracted lateron, say via regex_substring and replace, as Justin has suggested. But I hope you get the idea.

FWIW, to list the whole document, simply use the first OpenXML call (i.e. un-comment it).

Show replies
Show replies
VolkerBarth
Contributor
‎2020 Oct 16 10:00 AM
0 Likes

Here's a way to extract the text via further regexp_substr and sa_split_list - as so often, I'm impressed by the builtin tools...

create or replace variable varDoc xml;
set varDoc = 
'
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><title>
        </title>
        <style type="text/css">
            .csC583D0C8{text-align:left;text-indent:0pt;margin:12pt 0pt 12pt 0pt}
            .csCABEF5ED{color:#282832;background-color:transparent;font-family:Arial;font-size:10pt;font-weight:bold;font-style:normal;}
        </style>
    </head>
    <body>
        
Test row 1
Test row2
</body>
</html>';
select XmlText,
   -- search for text within the "span" tag
   regexp_substr(XmlText, '(?<=[<]span class="' || Class || '"[>]).*(?=[<]/span[>])') as TextExtract,
   line_num, row_value
-- filtered on the span node within the body's paragraphs
from OpenXML(varDoc, '//*:body/*:p/*:span')
   with (ID int '@mp:id',
      Node long varchar 'node()',
      Name long varchar '@mp:localname',
      Text long varchar 'text()',
      XmlText long varchar '@mp:xmltext',
      Class long varchar '@class')
   -- split TextExtract based on br tag
   outer apply sa_split_list(TextExtract, '
')
order by ID, line_num;

returns

XmlText;TextExtract;line_num;row_value
'Test row 1
Test row2';'Test row 1
Test row2';1;'Test row 1'
'Test row 1
Test row2';'Test row 1
Test row2';2;'Test row2'
Ask a Question