Extracting XML element value from Postgres

Question

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?

Community · Accepted Answer · 2017-05-23 12:32:42Z

3

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)

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Dec 28, 2012 at 14:25

Jeffrey Theobald

2,63720 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paulb Over a year ago

Thanks, it's almost there. I have multiple employees in the row, and as written, this gags after the first one with "Extra content at the end of the document." So I have to work on it. Having said that, your code is clearer in demonstration than this demo in the docs postgresql.org/docs/9.1/interactive/… ..also following the links in your reply lead to a helpful blog post scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres

Jeffrey Theobald Over a year ago

That may be because your original XML is invalid, there isn't a single root node. You have multiple <EMP /> objects, but nothing to wrap them, that's possibly causing the problem.

Collectives™ on Stack Overflow

Extracting XML element value from Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest