CODEWITHSUNDEEP
sqlxmlpostgresqlxml-parsing
Christian
Christian

Reputation: 7320

How to convert from XML to table?

29I have this XML string:

<CP>
  <V PV="1.29" PT="1.29" PB="1.29" ML="0.0" OB="Reg" />
  <V PV="0.77" PT="1.29" PB="1.29" ML="0.6" OB="Reg" />
  <V PV="0.77" PT="1.29" PB="0.65" ML="0.645" OB="Reg" />
</CP>

and I need generate a table (or a rowset) like this:

PV       PT      PB       ML      OB
numeric  numeric numeric  numeric text
-------- ------- -------- ------- ----
1.29     1.29    1.29     0.0     Reg 
0.77     1.29    1.29     0.6     Reg
0.77     1.29    0.65     0.645   Reg

Postgres 9.x

Upvotes: 1

Views: 92

Answers (1)

Christian
Christian

Reputation: 7320

Got it using xpath with @ to get attribute value and unnest:

select cast(cast((xpath('/V/@PV', node))[1] as text) AS numeric) as PV,
       cast(cast((xpath('/V/@PT', node))[1] as text) AS numeric) as PT,
       cast(cast((xpath('/V/@PB', node))[1] as text) AS numeric) as PB,
       cast(cast((xpath('/V/@ML', node))[1] as text) AS numeric) as ML,
       cast(cast((xpath('/V/@OB', node))[1] as text) AS text   ) as OB
from unnest(xpath('/CP/V',
'<CP><V PV="1.29" PT="1.29" PB="1.29" ML="0.0" OB="Reg" /><V PV="0.77" PT="1.29" PB="1.29" ML="0.6" OB="Reg" /><V PV="0.77" PT="1.29" PB="0.65" ML="0.645" OB="Reg" /></CP>'
)) node

Upvotes: 1

Related Questions