Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

Background

Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data.

This problem involves devising a generic solution to create XML documents based on mapping database tables (and JOIN conditions) to XPath expressions.

Problem

An XPath map codifies associations between an XML document and relational data as follows:

root               > people              -- "root" is a special keyword
person             > person              -- > is a node to table relation
person.first_name -> name/first          -- -> is a node/attribute to column relation
person.last_name  -> name/last
person.age        -> [@age]
account.person_id => person.person_id    -- => is a join
account            > person/account
account.number    -> [@id]

Where a PERSON table might resemble:

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

Where an ACCOUNT table might resemble:

account_id | person_id | number
         1 |       123 | 123456789

Calling a function using the XPath map would 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 and so the corresponding XML element (account) is not included in the final document.

This is a difficult problem and a complete solution is not necessary. A solution that handles 80% of simple tables mapped to simple XML elements and attributes would suffice.

Question

What technologies, or open source implementations, already perform such a task?

Barring that, what algorithm would return an XML document based on such a generic XPath map? The algorithm must transform the structure defined by the XPath map into an XML document with the content from the relations defined in the XPath map.

Related Links

Links that are somewhat related to this idea.

Articles and White Papers

Articles:

Commercial Software

Similar solutions:

share|improve this question
3  
This reads more like a project than a question. – Mark Storey-Smith Apr 8 at 16:56
1  
A mod? In which site? Do you mean you have 10k at SO - which gives you access to some of the mod tools? – ypercube Apr 8 at 17:16
A canonical answer cannot be provided as the implementation would differ between RDBMSs. Polling for existing solutions is off-topic. Nothing has been presented to demonstrate what you've tried so far. Please edit the question to narrow the scope of what you're asking. As it sits, I would vote to close, but can't because of the bounty. – Jon Seigel Apr 8 at 18:46
SQL server is a RDBMS that has built in XML functions, and it pretty much follows the XPath standard defined by the World Wide Web Consortium. – StanleyJohns Apr 8 at 20:31
@JonSeigel: That's the point of the question. We have ANSI SQL that defines how to write RDBMS-agnostic queries, but, as far as I can tell, there is no standard for mapping relationships to automatically produce XML documents. Effectively, to produce an XML document you have to write it yourself, which is time-consuming, error-prone, redundant, and not database-agnostic. How would you recommend I narrow the scope of the question? – Dave Jarvis Apr 8 at 20:50
show 2 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.