Convert column value with html tags into sql view with rows and columns

Question

I have a table named data with a column desc_data. The value of this column is as below :

<span class ="label">A</span><br> <span class ="value">A-Class</span> <span class ="label">B</span><br> <span class ="value">B-Class</span>.

I want to parse this column value,strip html tags and into a new view split it using sql query(Regexp_Replace maybe) such that: All label values become columns, i.e.

 A & B will become columns, and

A-Class & B-Class will become the column value respectively.

The actual data is much more with many labels and values however this is just sample to get help. The expected result should be:

View data_View

A          B
A-Class    B-Class

Sayan Malakshinov · Accepted Answer · 2020-07-17 12:03:27Z

1

I think it would be much more convenient to get required data as rows, not columns. And you can parse it using xmltable with a little modification of your original html (removing unclosed tags like  . that's why   is better):

with t as (
  -- your sample data:
  select
    q'[<span class ="label">A</span><br> <span class ="value">A-Class</span> <span class ="label">B</span><br> <span class ="value">B-Class</span>.
  ]' html_data
from dual
)
-- main query:
select xt.*
from t
    ,xmltable(
      'let $labels := /root/span[@class eq "label"]
       let $values := /root/span[@class eq "value"]
       for $label at $i in $labels
          return element label {
             attribute name {$label/text()}, 
             attribute value {$values[$i]/text()}
          }
       '
      passing
      xmltype(
       --- modify your html to make it compatible with xml:
       '<root>'
       || replace(replace(t.html_data,'<br>'),'&nbsp;')
       ||'</root>'
      )
      columns
         n for ordinality,
         label_name path '@name',
         label_value path '@value'
    ) xt;

Result:

         N LABEL_NAME                     LABEL_VALUE
---------- ------------------------------ ------------------------------
         1 A                              A-Class
         2 B                              B-Class

edited Jul 17, 2020 at 12:03

answered Jul 17, 2020 at 9:06

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

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

3 Comments

Velocity Over a year ago

Let us continue this discussion in chat.

Velocity Over a year ago

Hi Sayan, when i checked for bigger and real data. At palces it is throwing error. ex: when tag is something like Domain Production Project Team Asdsh jsdja kajdhjahdja Grueber, Here for label domain instead of the value Production, it takes the next tag's value,i.e. Asdsh jsdja kajdhjahdja Grueber which should have come against Project team

Velocity Over a year ago

basically a way to modify let $values := /root/span[@class eq "desc_value"], so that it also includes let $values := /root/span[@class eq " select desc_value"]

Barbaros Özhan · Accepted Answer · 2020-07-17 07:47:28Z

1

You need to split your string by some pattern( such as '/span> <span' ) recursively. Extract the desired column by using REGEXP_REPLACE() function, and then apply pivoting :

WITH t(desc_data) AS
(
 SELECT '<span class ="label">A</span><br> <span class ="value">A-Class</span> <span class ="label">B</span><br> <span class ="value">B-Class</span> <span class ="label">C</span><br> <span class ="value">C-Class</span>'
   FROM dual
), t2 AS
(
SELECT SUBSTR(desc_data,1,CASE WHEN INSTR(desc_data,'/span> <span',1,level) > 0
                               THEN INSTR(desc_data,'/span> <span',1,level) + 5 
                               ELSE LENGTH(desc_data)
                           END
             ) AS desc_data2
  FROM t
 CONNECT BY level <= REGEXP_COUNT(desc_data,'/span> <span') + 1
)
SELECT *
  FROM
  (
   SELECT REGEXP_REPLACE(desc_data2,'(.*"label">)(\S+)(</span>.*)','\2') AS label,
          REGEXP_REPLACE(desc_data2,'(.*"value">)(\S+)(</span>.*)','\2') AS value
     FROM t2 )
 PIVOT ( MAX(VALUE) FOR LABEL IN ('A' AS "A", 'B' AS "B", 'C' AS "C") );


A          B          C
-------    -------    -------
A-Class    B-Class    C-Class

Demo

answered Jul 17, 2020 at 7:47

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

1 Comment

Velocity Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Convert column value with html tags into sql view with rows and columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related