Convert HTML to XML and parse with
HTML and XML are both descendants of SGML.
From the sample data you have posted, it looks like you just need to remove the
<br> tags and wrap
desc_data in a root element (eg
<row>) for the conversion into XML.
Once in XML format, you can parse the data with
XMLTABLE() to give you:
- instance of
<span> ( via
for ordinality, column name
- value of
- value of
This is done within the CTE named
xml_data in my example.
value always follows
label, you can convert the instance of
RN ) to have the same value for each pair. (this is also done in
From there, you can
PIVOT the data into a table that has:
- your row identifier (
- value for
LABEL is a key word so I used
- value for
VALUE is a key word so I used
This is done within the CTE labeled
key_val in my example.
For the final query, the final columns names MUST BE KNOWN at compile time. Once you know the columns, you can just do another
PIVOT for a proper table.
As you can see, HTML is not a good data format to use for parsing data and/or using the data within a computer (eg a database).
desc_data is used for display, I recommend that the data is stored in XML or JSON format. (I'm assuming you can change the source)
Once data is in a parseable format, PL/SQL is no longer needed.
with data as (
select q'[<span class ="label">Project Name</span><br> <span class ="value">Digital</span> <span class ="label">Dept Name</span><br> <span class ="value">Finance</span><span class ="label">Level</span><br> <span class ="value">Level 5</span>]'
,1 AS RID
),xml_data as (
select RID, a.rn - mod(a.rn+1,2) rn,a.label_txt, a.val
from data d, xmltable( '/row/span'
passing xmltype( '<row>' || replace(d.txt,'<br>','') || '</row>' )
rn for ordinality,
label_txt varchar2(20) path '/span/@class',
val varchar2(20) path '/span'
), key_val as (
select rid, label_txt, value_txt
min( val )
for label_txt in ( 'label' as LABEL_TXT, 'value' as VALUE_TXT)
select RID, PNAME, LVL, DNAME
min( VALUE_TXT )
for LABEL_TXT in ('Project Name' as PNAME, 'Level' as LVL, 'Dept Name' as DNAME )
B? +1 for an interesting tricky question - I'll have a bash - Oracle isn't really my thing, but I could do with practicing my regexes!