Thread: Map table data to XPath expressions
Hi, all.
Am wondering if a feature exists in PostgreSQL that allows mapping tables and columns to XPath expressions. For example, consider the following map, "person-account":
This would allow the following query (or similar):
And produce the following string (or rows of strings that the calling client needs to append):
I know it can be built using the XML functions that exist in PostgreSQL 9. I am looking for a solution that would not entail having to write a fair bit of code, such as (without the joining to the account table):
Thank you!
Am wondering if a feature exists in PostgreSQL that allows mapping tables and columns to XPath expressions. For example, consider the following map, "person-account":
person -> people
person.first_name -> person/name/first
person.last_name -> person/name/last
person.age -> person[@age]
account.person_id => person.person_id -- JOIN clause
account.number -> person/account[@id]
person.first_name -> person/name/first
person.last_name -> person/name/last
person.age -> person[@age]
account.person_id => person.person_id -- JOIN clause
account.number -> person/account[@id]
This would allow the following query (or similar):
SELECT * FROM xml.person-account;
And produce the following string (or rows of strings that the calling client needs to append):
<people>
<person age="18">
<name>
<first>Peter</first>
<last>Parker</last>
</name>
<account id="123456789" />
</person>
<!-- etc. --->
</people>
<person age="18">
<name>
<first>Peter</first>
<last>Parker</last>
</name>
<account id="123456789" />
</person>
<!-- etc. --->
</people>
I know it can be built using the XML functions that exist in PostgreSQL 9. I am looking for a solution that would not entail having to write a fair bit of code, such as (without the joining to the account table):
SELECT
xmlelement( name "people",
xmlagg(
xmlelement( name "person",
xmlattributes(age),
xmlforest( first_name as first, last_name as last )
)
)
)
FROM person;Thank you!