XML to create from an oracle query

Question

I wrote below query to generate a xml file

 SELECT XMLElement("Providers", 
                   XMLElement("Provider" ,XMLAttributes(
                                        "Unique ID" as "UniqueId"), 
                   XMLElement("ProviderType", "Provider Type"),
                   XMLElement("Specialities",
                   XMLElement("Speciality", "Specialty")),
                   XMLElement("Relationships",
                   XMLElement("Relationship", "Relationship")),
                   XMLElement("ServiceAddress",
                   XMLElement("AddressLine1","P Address Line 1"),
                   XMLElement("AddressLine2","P Address Line 2"), 
                   XMLElement("AddressLine3","P Address Line 3"), 
                   XMLElement("AddressLine4","P Address Line 4"),
                   XMLElement("City","Service city description"),
                   XMLElement("Region","PState_Province_Region"),
                   XMLElement("PostalCode","PPostal Code"),
                   XMLElement("IsoCountryCode","ServiceCountry ISO code")),
                   XMLElement("Contacts",
                        XMLElement("ContactInfo",
                            XMLElement("ContactType","Contact Type"),
                            XMLElement("Name","Contact Name"),
                            XMLElement("Title","Contact Title"),
                            XMLElement("Function","Contact Function"),
                            XMLElement("PhoneNo","Contact Phone Number"),
                            XMLElement("CellNo","Contact Cell Phone Number"),
                            XMLElement("Email","Contact E-mail") ))))
                    AS "RESULT" 
FROM providers_xml

but this create seperate xml file for each row in the table provider as below

file 1

<?xml version="1.0"?>
-<Providers>
 -<Provider UniqueId="AA">
  <ProviderType>1</ProviderType>
  -<Specialities>
   <Speciality>210</Speciality>
  </Specialities>
  -<Relationships>
   <Relationship/></Relationships>
  -<ServiceAddress>
   <AddressLine1>ABC</AddressLine1>
   <AddressLine2/><AddressLine3/>
   <AddressLine4/>
   <City>city123</City>
   <Region/>
   <PostalCode/>
   <IsoCountryCode>US</IsoCountryCode>
   </ServiceAddress>
  -<Contacts>
   -<ContactInfo>
    <ContactType>1</ContactType>
    <Name>General Contact</Name>
    <Title/>
    <Function/>
    <PhoneNo/>
    <CellNo/>
    <Email/>
    </ContactInfo>
   </Contacts>
  </Provider>
</Providers>

File 2

<?xml version="1.0"?>
-<Providers>
 -<Provider UniqueId="ABC">
  <ProviderType>1</ProviderType>
  -<Specialities>
   <Speciality>100</Speciality>
  </Specialities>
  -<Relationships>
   <Relationship/></Relationships>
  -<ServiceAddress>
   <AddressLine1>zxy</AddressLine1>
   <AddressLine2/><AddressLine3/>
   <AddressLine4/>
   <City>city300</City>
   <Region/>
   <PostalCode/>
   <IsoCountryCode>US</IsoCountryCode>
   </ServiceAddress>
  -<Contacts>
   -<ContactInfo>
    <ContactType>2</ContactType>
    <Name>General Contact</Name>
    <Title/>
    <Function/>
    <PhoneNo/>
    <CellNo/>
    <Email/>
    </ContactInfo>
   </Contacts>
  </Provider>
</Providers>

But i need all these files as one as below

-<Providers>
     -<Provider UniqueId="AA">
      <ProviderType>1</ProviderType>
      -<Specialities>
       <Speciality>210</Speciality>
      </Specialities>
      -<Relationships>
       <Relationship/></Relationships>
      -<ServiceAddress>
       <AddressLine1>ABC</AddressLine1>
       <AddressLine2/><AddressLine3/>
       <AddressLine4/>
       <City>city123</City>
       <Region/>
       <PostalCode/>
       <IsoCountryCode>US</IsoCountryCode>
       </ServiceAddress>
      -<Contacts>
       -<ContactInfo>
        <ContactType>1</ContactType>
        <Name>General Contact</Name>
        <Title/>
        <Function/>
        <PhoneNo/>
        <CellNo/>
        <Email/>
        </ContactInfo>
       </Contacts>
      </Provider>
     -<Provider UniqueId="ABC">
      <ProviderType>1</ProviderType>
      -<Specialities>
       <Speciality>100</Speciality>
      </Specialities>
      -<Relationships>
       <Relationship/></Relationships>
      -<ServiceAddress>
       <AddressLine1>zxy</AddressLine1>
       <AddressLine2/><AddressLine3/>
       <AddressLine4/>
       <City>city300</City>
       <Region/>
       <PostalCode/>
       <IsoCountryCode>US</IsoCountryCode>
       </ServiceAddress>
      -<Contacts>
       -<ContactInfo>
        <ContactType>2</ContactType>
        <Name>General Contact</Name>
        <Title/>
        <Function/>
        <PhoneNo/>
        <CellNo/>
        <Email/>
        </ContactInfo>
       </Contacts>
      </Provider>
    </Providers>

Please help to get the desired o/p

Sounds like you're after [XMLAGG](https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions235.htm) — Boneist, Mar 28 '17 at 14:08
`select xmlelement("Providers",xmlagg( 'rest_of_your_sql' )) as result ` — Arkadiusz Łukasiewicz, Mar 28 '17 at 14:10

score 0 · Answer 1 · answered Apr 10 '17 at 16:01

0

Add an XMLAGG clause.

 SELECT XMLElement("Providers", 
            **XMLAGG(**
               XMLElement("Provider" ,XMLAttributes(
                                    "Unique ID" as "UniqueId

answered Apr 10 '17 at 16:01

mark d drake

1,280
12
20

XML to create from an oracle query

1 Answers1