insert into cdy_list (list_code)
select * from (
select '<GLOBALLIST name=' || '"' || 'Products' || '">'
from products
where rownum = 1

union all select
'<LISTITEM value=' || '"' || name || '"/>'
from products
where visible = 'Y'

union ALL select
'</GLOBALLIST>'
FROM PRODUCTS
WHERE ROWNUM = 1) ;

insert into cdy_list (list_code)
select listagg(val) within group (order by rn)
from (
select 1 rn, '<GLOBALLIST name=' || '"' || 'Products' || '">' val
from products
where rownum = 1
union all select 2, '<LISTITEM value=' || '"' || name || '"/>'
from products
where visible = 'Y'
union ALL select 3, '</GLOBALLIST>'
FROM PRODUCTS
WHERE ROWNUM = 1)
/

select  xmlserialize(
                     document
                     xmlelement("GLOBALLIST",
                                xmlattributes('Products' as "product"),
                                xmlagg(
                                       xmlelement(
                                                  "LISTITEM",
                                                  xmlattributes(name as "value")
                                                 )
                                      )
                               )
                     indent size = 2
                     ) xml
  from  products
/

SQL> set long 10000
SQL> select  xmlserialize(
  2                       document
  3                       xmlelement("GLOBALLIST",
  4                                  xmlattributes('Products' as "product"),
  5                                  xmlagg(
  6                                         xmlelement(
  7                                                    "LISTITEM",
  8                                                    xmlattributes(prod_name as "value")
  9                                                   )
 10                                        )
 11                                 )
 12                       indent size = 2
 13                       ) xml
 14    from  sh.products
 15  /

XML
--------------------------------------------------------------------------------
<GLOBALLIST product="Products">
  <LISTITEM value="5MP Telephoto Digital Camera"/>
  <LISTITEM value="17&quot; LCD w/built-in HDTV Tuner"/>
  <LISTITEM value="Envoy 256MB - 40GB"/>
  <LISTITEM value="Y Box"/>
  <LISTITEM value="Mini DV Camcorder with 3.5&quot; Swivel LCD"/>
  <LISTITEM value="Envoy Ambassador"/>
  <LISTITEM value="Laptop carrying case"/>
  <LISTITEM value="Home Theatre Package with DVD-Audio/Video Play"/>
  <LISTITEM value="18&quot; Flat Panel Graphics Monitor"/>
  <LISTITEM value="Envoy External Keyboard"/>
  <LISTITEM value="External 101-key keyboard"/>
  <LISTITEM value="PCMCIA modem/fax 28800 baud"/>
  <LISTITEM value="SIMM- 8MB PCMCIAII card"/>
  <LISTITEM value="SIMM- 16MB PCMCIAII card"/>
  <LISTITEM value="Multimedia speakers- 3&quot; cones"/>
  <LISTITEM value="Unix/Windows 1-user pack"/>
  <LISTITEM value="8.3 Minitower Speaker"/>
  <LISTITEM value="Mouse Pad"/>
  <LISTITEM value="1.44MB External 3.5&quot; Diskette"/>
  <LISTITEM value="Multimedia speakers- 5&quot; cones"/>
  <LISTITEM value="PCMCIA modem/fax 19200 baud"/>
  <LISTITEM value="External 6X CD-ROM"/>
  <LISTITEM value="External 8X CD-ROM"/>
  <LISTITEM value="Envoy External 6X CD-ROM"/>
  <LISTITEM value="Envoy External 8X CD-ROM"/>
  <LISTITEM value="Internal 6X CD-ROM"/>
  <LISTITEM value="Internal 8X CD-ROM"/>
  <LISTITEM value="O/S Documentation Set - English"/>
  <LISTITEM value="O/S Documentation Set - German"/>
  <LISTITEM value="O/S Documentation Set - French"/>
  <LISTITEM value="O/S Documentation Set - Spanish"/>
  <LISTITEM value="O/S Documentation Set - Italian"/>
  <LISTITEM value="O/S Documentation Set - Kanji"/>
  <LISTITEM value="Standard Mouse"/>
  <LISTITEM value="Deluxe Mouse"/>
  <LISTITEM value="Keyboard Wrist Rest"/>
  <LISTITEM value="CD-R Mini Discs"/>
  <LISTITEM value="Music CD-R"/>
  <LISTITEM value="CD-RW, High Speed, Pack of 10"/>
  <LISTITEM value="CD-RW, High Speed Pack of 5"/>
  <LISTITEM value="CD-R, Professional Grade, Pack of 10"/>
  <LISTITEM value="OraMusic CD-R, Pack of 10"/>
  <LISTITEM value="CD-R with Jewel Cases, pACK OF 12"/>
  <LISTITEM value="DVD-R Disc with Jewel Case, 4.7 GB"/>
  <LISTITEM value="DVD-RAM Jewel Case, Double-Sided, 9.4G"/>
  <LISTITEM value="DVD-R Discs, 4.7GB, Pack of 5"/>
  <LISTITEM value="DVD-R Discs, 4.7GB, Pack of 5"/>
  <LISTITEM value="DVD-RW Discs, 4.7GB, Pack of 3"/>
  <LISTITEM value="3 1/2&quot; Bulk diskettes, Box of 50"/>
  <LISTITEM value="3 1/2&quot; Bulk diskettes, Box of 100"/>
  <LISTITEM value="Model CD13272 Tricolor Ink Cartridge"/>
  <LISTITEM value="Model SM26273 Black Ink Cartridge"/>
  <LISTITEM value="Model NM500X High Yield Toner Cartridge"/>
  <LISTITEM value="Model A3827H Black Image Cartridge"/>
  <LISTITEM value="Model K3822L Cordless Phone Battery"/>
  <LISTITEM value="Model C9827B Cordless Phone Battery"/>
  <LISTITEM value="Model K8822S Cordless Phone Battery"/>
  <LISTITEM value="Model C93822D Wireless Phone Battery"/>
  <LISTITEM value="S27273M Extended Use w/l Phone Batt."/>
  <LISTITEM value="64MB Memory Card"/>
  <LISTITEM value="128MB Memory Card"/>
  <LISTITEM value="256MB Memory Card"/>
  <LISTITEM value="Bounce"/>
  <LISTITEM value="Endurance Racing"/>
  <LISTITEM value="Smash up Boxing"/>
  <LISTITEM value="Martial Arts Champions"/>
  <LISTITEM value="Comic Book Heroes"/>
  <LISTITEM value="Fly Fishing"/>
  <LISTITEM value="Finding Fido"/>
  <LISTITEM value="Adventures with Numbers"/>
  <LISTITEM value="Extension Cable"/>
  <LISTITEM value="Xtend Memory"/>
</GLOBALLIST>


SQL>