Shredding XML in postgresql

Question

In SQL Server 2005's T-SQL language I can shred XML value the following way:

SELECT
    t.c.value('./ID[1]', 'INT'),
    t.c.value('./Name[1]', 'VARCHAR(50)')
FROM @Xml.nodes('/Customer') AS t(c)

where @Xml is a xml value something like

'<Customer><ID>23</ID><Name>Google</Name></Customer>'

Can someone help me to achieve the same result in PostgreSQL (probably in PL/pgSQL)?

Answer 1

Use the xpath-function in PostgreSQL to proces the XML.

Edit: Example:

SELECT
    xpath('/Customer/ID[1]/text()', content),
    xpath('/Customer/Name[1]/text()', content),
    *
FROM
    (SELECT '<Customer><ID>23</ID><Name>Google</Name></Customer>'::xml AS content) AS sub;

Answer 2

The xpath function will return an array of nodes so you can extract multiple partners. Typically you would do something like:

SELECT (xpath('/Customer/ID/text()', node))[1]::text::int AS id,
  (xpath('/Customer/Name/text()', node))[1]::text AS name,
  (xpath('/Customer/Partners/ID/text()', node))::_text AS partners
FROM unnest(xpath('/Customers/Customer',
'<Customers><Customer><ID>23</ID><Name>Google</Name>
 <Partners><ID>120</ID><ID>243</ID><ID>245</ID></Partners>
</Customer>
<Customer><ID>24</ID><Name>HP</Name><Partners><ID>44</ID></Partners></Customer>
<Customer><ID>25</ID><Name>IBM</Name></Customer></Customers>'::xml
)) node

Where you use unnest(xpath()) to break a big chunk of xml into row sized bites; and xpath()1 to extract individual values. Extracting the first value from an xml array, casting to text and then casting to int (or date, numeric, etc) is a real PITA. I have some helper functions on my blog to make this easier. XML Parsing in Postgres