Convert converted XPath expressions to XML containing relational data

Question

Background

Using XML and XSL to create a website. The PostgreSQL database is structured to represent the necessary XML documents. PostgreSQL offers a number of XML-related functions, listed in:

http://www.postgresql.org/docs/current/static/functions-xml.html

This is a different problem than moving from XML to a database, from which there are many solutions.

Update: To clarify, this problem is not related to the use of the XML functions listed in this URL. It includes a one-stop solution for creating XML documents based on matching database tables (and JOIN conditions) with XPath expressions.

Update:. For further refinement, XQuery is designed to search for XML documents as if they were a database — like crossroads between XPath and SQL. I have a database and I want to create an XML document. The structure of an XML document should be provided in terms of XPath expressions mapped to tables and columns, and not in terms of XML functions.

Problem

The problem I would like to solve is comparing the values ​​in the table rows with XPath values ​​using PostgreSQL. The example below illustrates the problem.

Example

The XPath card this issue relates to will resemble:

 root > people person > person person.first_name -> name/first person.last_name -> name/last person.age -> [@age] account.person_id => person.person_id account > person/account account.number -> [@id] 

Table

A PERSON may resemble:

 person_id | first_name | last_name | age 123 | Peter | Parker | 18 456 | James | Jameson | 42 

And the ACCOUNT table may resemble:

 account_id | person_id | number 1 | 123 | 123456789 

Querying a database using an XPath map will produce the following XML document:

 <people> <person age="18"> <name> <first>Peter</first> <last>Parker</last> </name> <account id="123456789" /> </person> <person age="42"> <name> <first>James</first> <last>Jameson</last> </name> </person> </people> 

In this case, James Jameson does not have an account, so the corresponding XML element ( ACCOUNT ) is not included in the final document.

This is a complex problem and a complete solution is not required. A solution that processes 80% of simple tables mapped to simple XML elements and attributes will suffice.

Question

How would you create an SQL statement (or procedure) that returns an XML document based on a mapping of database tables (and several JOIN operations) to XPaths (or similar) to perform data conversion?

Are there any open source technologies or implementations that already perform such tasks?

Thanks!

Related Links

Articles and white papers

• http://www.ibm.com/developerworks/library/x-query2xml/
• http://www.thinkmind.org/download.php?articleid=dbkda_2011_6_20_30152
• http://www.informatica.si/PDF/33-3/06_Naser%20-%20Two-Way%20Mapping%20between%20Object-Oriented%20Databases%20and%20XML.pdf
• http://www.stylusstudio.com/sqlxml_tutorial.html

Commercial software

• http://www.stylusstudio.com/db_to_xml_mapper.html
• http://www.altova.com/mapforce.html

Answer 1

It should use xml generation functions

 postgres=# select xmlelement(name "people", xmlagg(xmlelement(name "person", xmlattributes(age), xmlforest(first_name as first, last_name as last)))) from person; xmlelement ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── <people><person age="18"><first>Peter</first><last>Parker</last></person><person age="42"><first>James</first><last>Jameson</last></person></people> 

(1 line)

there is another possibility of using

 postgres=# select table_to_xml('person', true, false, ''); table_to_xml ──────────────────────────────────────────────────────────────── <person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">↵ ↵ <row> ↵ <first_name>Peter</first_name> ↵ <last_name>Parker</last_name> ↵ <age>18</age> ↵ </row> ↵ ↵ <row> ↵ <first_name>James</first_name> ↵ <last_name>Jameson</last_name> ↵ <age>42</age> ↵ </row> ↵ ↵ </person> ↵ (1 row) 

and the next step should be some conversion of xslt to your target format - xslt experts will be much better here and should help