oracle html解析,SQL解析HTML代码示范（众人拾柴火焰高）

WITH t AS (SELECT

XMLTYPE('

'

厦 shà / xià 许 xǔ / hǔ 识 shí / zhì 咽 yān / yàn / yè 哇 着 zhuó / zháo / zhāo / zhe'

||'

') htmlstr

FROM dual

)

SELECT hz, trim(rtrim(regexp_substr(py || '/', '.*?' || '/', 1, LEVEL),'/')) py ,LEVEL POS FROM

(

SELECT rn,PRIOR str hz,str py FROM

(

SELECT  ROWNUM rn, str from t,

XMLTable(

'table/tr/td'

passing t.htmlstr

COLUMNS

str VARCHAR2(100) path 'a[1]'

)

) x WHERE level=2 START WITH mod(rn,2)=1 CONNECT BY PRIOR rn =  rn-1 AND LEVEL<=2

)

CONNECT BY  rn = PRIOR rn

AND PRIOR dbms_random.value IS NOT NULL

AND LEVEL <= regexp_count(py,'/')+1