I am using xpath queries against an XML column like so:
annodb=# select id,xpath('//LastName/text()',content) from medlinexml
limit 10;
id | xpath
----------+----------------------------
12236137 | {Mikulicz-radecki,Freund}
12305346 | {Ota}
12332250 | {Bernstein}
12255346 | {Novak}
12255369 | {Kurzrok}
12332284 | {Davis}
12332297 | {Allen,Pratt,Newell,Bland}
12305416 | {Goldstein}
12305417 | {Seibels}
12305424 | {Richardson}
(10 rows)
I would like to build a view where each id is associated with a text
representation of each last name. So, the view would look like:
12236137 | Mikulicz-radecki
12236137 | Freund
12305436 | Ota
And so on. Is there a way to do this without a pl/pgsql loop?
Thanks,
Sean