Oracle Server - How do I format this query to see each XML document from start to end?

Author

How do I format this query to see each XML document from start to end?

g3000

2005-05-26, 11:23 am

I have the following query that returns 23 rows as individual XML docs
but I can only see part of each document. I would really like all 23
rows to be within this tag
<Provider version=9.01> ..... </Provider>

I think I need to use xmlagg before HeaderInfo element
select xmlelement( "provider",
xmlattributes( '09.01.00' as "version"),
xmlelement("HeaderInfo",
xmlforest(cmd_seq as "cmd_seq",
corps_id as "corps_id",
dea_id as "dea_id",
entity_seq as "entity_seq",
facility_uic as "facility_uic")),
xmlelement("Detail",
xmlforest(fname||' '||lname||''||suffix
as "Name",
paygrade_id as "Paygrade",
rate as "Rate",
ssn as "SSN",
start_date as "Start Date",
stop_date as "Stop Date",
username as "Username",
view_sens_ind as "Sensitive")))
from sams.provider

returns .....

XMLELEMENT("PROVIDER",XMLATTRIBUTES('09.01.00'AS"VERSION"),XMLELEMENT("HEADERINF

--------------------------------------------------------------------------------

<provider
version="09.01.00"><HeaderInfo><entity_seq>855</entity_seq><facility_u

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>BSC</co

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>MC</cor

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor

<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>MC</cor

..... ( 23 rows returned )

g3000

2005-05-26, 1:23 pm

Allow me to clear up my question, I desire a doc like:
<Provider version="9.01">
<HeaderInfo>
cmd_seq
corps_id
dea_id
entity_seq
facility_uic
</HeaderInfo>
<Detail>
paygrade
rate
ssn
start_date
stop_date
username
sensitive
</Detail>
<HeaderInfo>
......
</HeaderInfo>
<Detail>
....
</Detail>
....... Header and Detail for each row
</Provider>

g3000

2005-05-26, 8:23 pm

I figured out I need to set long, my real goal was to get my format to
be like what I listed in my second post instead I get

SQL> select xmlelement( "provider",
2 xmlattributes( '09.01.00' as "version"),
3 xmlelement("HeaderInfo",
4 xmlforest(cmd_seq as "cmd_seq",
5 corps_id as "corps_id",
6 dea_id as "dea_id",
7 entity_seq as "entity_seq",
8 facility_uic as "facility_uic")),
9 xmlelement("Detail",
10 xmlforest(fname||' '||lname||''||suffix
as "Name",
11 paygrade_id as "Paygrade",
12 rate as "Rate",
13 ssn as "SSN",
14 start_date as "Start Date",
15 stop_date as "Stop Date",
16 username as "Username",
17 view_sens_ind as "Sensitive")))
18 from sams.provider order by cmd_seq;

XMLELEMENT("PROVIDER",XMLATTRIBUTES('09.01.00'AS"VERSION"),XMLELEMENT("HEADERINF
--------------------------------------------------------------------------------
<provider
version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor
ps_id><entity_seq>861</entity_seq><facility_uic>00000000</facility_uic></HeaderIn
fo><Detail><Name>CHRIS
BOYLES</Name><Paygrade>CIV</Paygrade><Rate>CIV</Rate><SS
N>001010001</SSN><Start_x0020_Date>01-JAN-00</Start_x0020_Date><Username>QUACK_1
67</Username><Sensitive>Y</Sensitive></Detail></provider>

I want
<Provider version="9.01">
<HeaderInfo>
</HeaderInfo>
<Detail>
</Detail>
( a header and detail for each row, all rows enclosed in Provider
tags )
</Provider>

any ideas?

g3000

2005-05-27, 7:23 am

thanks for the response,
Im not worried about the result being formatted like I wrote in psudo
xml I just want the complete document to adhere to the psuedo xml's
schema.
I just wanted all my rows to be inside of the <Provider> </Provider>
tags.
Not a <Provider> </Provider> for each row. thats all.

I think I need to think more about the query first then SQLX functions
next.

I may use a User Defined Type to get the format I want for the more
complex queries.
Just dont want to have a UDT for each table.
I would like to get it with just a query.

IF somebody writes a good SQLX book it will have good sales.

I see threads with some of the same "fuzziness" on certain uses of
SQLX.

Sponsored Links