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

g3000 wrote:
> 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 )

set long 100000

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)