Parse html field in postgresql query

Question

I have a text field that contains an HTML table in a PostgreSQL database. I would like to extract some data from this field: each TR (not from the header) in a row and each TD in a column. Is that possible?

The name of a table is "documentos" and the name of the text field that contains the HTML table is called "props". Props contains following:

select props
from documentos
where uidref = 'ee41f201-0049-41e9-9c5d-5c35e2cf73ac'

And I would like obtain:

444444444 | Investigador | Daniel | Perez
555555555 | Becario      | Jorge  | Fernandez

Thanks in advance!

Possible duplicate of How do I parse XML in Python?

PaulMcG
– PaulMcG

2018-10-26 19:36:50 +00:00
Commented Oct 26, 2018 at 19:36 — PaulMcG
– PaulMcG, Commented Oct 26, 2018 at 19:36
Maybe this question could be of help.

Andrew
– Andrew

2018-10-26 20:08:09 +00:00
Commented Oct 26, 2018 at 20:08 — Andrew
– Andrew, Commented Oct 26, 2018 at 20:08

Andrew · Accepted Answer · 2018-10-27 01:48:42Z

I have no experience with PostreSQL and very little with XPATH, but I was able to get something for you:

with x as (select
'<TABLE>
        <TBODY>
            <TR>
                <TH class="RowTitle">Identificacion</TH>
                <TH class="colRol">Rol</TH>
            </TR>
            <TR class="tData">
                <TD class="RowTitle">
                    <A href="#">4444</A>
                </TD>
                <TD class="colRow" val="INVARGEXT">Investigador</TD>
            </TR>
            <TR class="tData">
                <TD class="RowTitle">
                    <A href="#">55555</A>
                </TD>
                <TD class="colRow" val="BECARIO">Becario</TD>
            </TR>
        </TBODY>
    </TABLE>'::xml as t
),
y as (select unnest(xpath('//TR[@class="tData"]', t)) td from x)
select -- y.td, -- just to debug
xpath('//TD[@class="RowTitle"]/A/text()', y.td),
xpath('//TD/text()', y.td)
from y;

This outputs:

    xpath   xpath
1   4444                                        Investigador
2   55555                                       Becario

I hope this can be of use.

More information here and here.

Collectives™ on Stack Overflow

Parse html field in postgresql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related