CODEWITHSUNDEEP
postgresqlxpathxml-parsingxqueryplpgsql
Paulb
Paulb

Reputation: 1531

Extracting XML element value from Postgres

Given:

CREATE TABLE xmltest(xtxt xml);

And:

INSERT INTO xmltest values ('<EMP><NAME>Mike</NAME><HIREDATE>12-FEB-96</HIREDATE></EMP><EMP><NAME>Bob</NAME><HIREDATE>13-AUG-97</HIREDATE></EMP><EMP><NAME>Paul</NAME><HIREDATE>17-JUN-94</HIREDATE></EMP><EMP><NAME>Jim</NAME><HIREDATE>01-JUN-94</HIREDATE></EMP>');

Using the base functionality of Postgres 9.2, how would I write a SELECT statement that returned only the employee names, 1 name per row in the result set? Or would I have to write a function in PL/PGSQL to do that?

Upvotes: 2

Views: 7041

Answers (1)

Jeffrey Theobald
Jeffrey Theobald

Reputation: 2617

You can extract fields of interest into an array using the xpath function, and then from there you can use the unnest builtin to split this array into multiple rows:

SELECT unnest(xpath('//name', xtxt))
FROM   xmltest;

(Slightly borrowed from this question)

Upvotes: 3

Related Questions