Oracle: Split html string into multiple rows/columns?

Question

I have a table in Oracle where some rows have a column with embedded HTML string. I'd like to know the SQL required so that I can split the string into multiple fields/rows. The table example looks like this:

ID	HTML_Content
1	`<div>Where would you like to go today?<ul><li><a href="#" data-jump="1234567">Customer Service</a></li><li><a href="#" data-jump="2134583">Technical Service</a></li><li><a href="#" data-jump="8881234">Human Resources</a></li></ul></div>`

I'd like the output to look like:

ID	Data_Jump	Content
1	1234567	Customer Service
1	2134583	Technical Service
1	8881234	Human Resources

Thank you!

OldProgrammer · Accepted Answer · 2022-08-03 23:27:23Z

1

If you can guarantee that the HTMl can be parsed as XML ( all enclosing tags have and end tag), then you can do something like this:

with test_data as
( select
xmltype('<div>Where would you like to go today?<ul><li><a href="#" data-jump="1234567">Customer Service</a></li><li><a href="#" data-jump="2134583">Technical Service</a></li><li><a href="#" data-jump="8881234">Human Resources</a></li></ul></div>') vals
from dual)
select x.* from test_data,
 XMLTABLE ('//li'
                    PASSING vals
                    COLUMNS
                      data_jump VARCHAR2(30) PATH 'a/@data-jump',
                      content   varchar2(500) PATH '*'
                    ) x

answered Aug 3, 2022 at 23:27

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Oracle: Split html string into multiple rows/columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related