Formatting for XSU output

Hi all,

I spent today in a meeting with our clients, attempting to help them with an issue that involves the Oracle XML-SQL Utility. They're happy with the database, but have some concerns about the application in progress. Part of our committment to them involves allowing our application to summarize and report, on a yearly basis to a government body here in Canada.

     This government body needs to accept data from a variety of sources in a format that meets their standard.
In this case it must meet an XML schema that they have provided us.

I worte a package that will, on demand, transform the data in our database, so that a set of staging tables will be populated to meet their reporting requirements..that part is fine..the package works, they've validated the data, and all the summaries they need are set to go. The design specs call for the use of the XSU to select the data from the staging tables, convert it to XML, load it into a file and submit it.
The question I have for this group is the following:

Given a one to many relationship (lets go with DEPT and EMP tables)..I'm finding that the schema they require for reporting is such that I can't send records the way I would in a normal equijoinn..

I can' report something like:

SELECT D.DEPTNO, E.ENAME FROM DEPT D, EMP E WHERE E.DEPTNO = D.DEPTNO because I'll get:

20 Smith
20 Jones
20 Blake

What I need to get, for the XSU utility to reflect it correctly is:

20 Smith Jones Blake

I've started to investigate some Oracle packages like XSDPROCESSOR to see if it's possible to easily do this type of transformation...I'm thinking I could do it myself programatically, but I don't want to reinvent the wheel and, after all this rambling, here's my question:

Given that one-to-many relationships is the most common in a RDBMS, and that (at least in my country) there seems to be a certain government standard of reporting 1:M in a "spreadsheet" sort of format as I've listed above, is there an internal Oracle procedure for transforming:

a b
a c
a d

b e
b f

into
a b c d
b e f
?

One side of me (the side I'm paid for) tells me I've given them the data they need and they agree to that part...I'm just wondering if I can give the developer people any further insight to help them transform the data into a reporting format they need?

Any thoughts or direction on docs to rtfm are deeply appreciated and welcome.

Best regards,

Steve

Oracle 8.1.7, NT, OAS 9.2, Designer 9i Received on Wed Aug 13 2003 - 20:44:16 CDT