Monthly Archives: February 2013

Workout with XML –

Let us revise some of the powerful XML DB features.
1] Creating a test table 'test_xml'
create table test_xml
as
select 1 id,'sam' name,'venice' place,'30 Lpa' sal,'FERRARI' car1,'AUDI' car2  from dual
UNION ALL
select 2 id,'mann' name,'paris' place,'30 Lpa' sal,'FERRARI' car1,'BMW' car2  from dual
UNION ALL
select 3 id,'rath' name,'amsterdam' place,'35 Lpa' sal,'AUDI' car1,'Rolls Royce' car2  from dual
UNION ALL
select 4 id,'patel' name,'italy' place,'35 Lpa' sal,'AUDI' car1,'FERRARI' car2  from dual
UNION ALL
select 5 id,'soni' name,'espanol' place,'38 Lpa' sal,'BMW' car1,'AUDI' car2  from dual
UNION ALL
select 6 id,'rose' name,'new york' place,'38 Lpa' sal,'AUDI' car1,'Rolls Royce' car2  from dual
UNION ALL
select 7 id,'ranit' name,'chennai' place,'4 Lpa' sal,'NA' car1,NULL  from dual;

2] Suppose, this is the required XML structure -

 --->>-- XML structure
<FRIENDS>
 <Friend id="">
     <Name></Name>
     <Place></Place>
     <Salary></Salary>
     <Cars>
         <Car_1></Car_1>
         <Car_2></Car_2>
     </Cars>
 </Friend>
</FRIENDS>

3] So, we write a query like this -

select
     XMLElement("FRIENDS",
     XMLAgg(    --->>-- to Aggregate all '<Friend>' nodes as one and place under the root '<FRIENDS>' tag
         XMLElement("Friend",
         XMLAttributes(a.id "id"),
             XMLForest(a.name "Name", a.place "Place", a.sal "Salary",
                XMLForest(a.car1 "Car_1", a.car2 "Car_2")  as "Cars"
             )
         ) order by a.id    --->>-- to sort the entries
      )
     )
 from
 test_xml a;

4] And we get the below XML -

<FRIENDS>
    <Friend id="1">
        <Name>sam</Name>
        <Place>venice</Place>
        <Salary>30 Lpa</Salary>
        <Cars>
            <Car_1>FERRARI</Car_1>
            <Car_2>AUDI</Car_2>
        </Cars>
    </Friend>
    <Friend id="2">
        <Name>mann</Name>
        <Place>paris</Place>
        <Salary>30 Lpa</Salary>
        <Cars>
            <Car_1>FERRARI</Car_1>
            <Car_2>BMW</Car_2>
        </Cars>
    </Friend>
    <Friend id="3">
        <Name>rath</Name>
        <Place>amsterdam</Place>
        <Salary>35 Lpa</Salary>
        <Cars>
            <Car_1>AUDI</Car_1>
            <Car_2>Rolls Royce</Car_2>
        </Cars>
    </Friend>
    <Friend id="4">
        <Name>patel</Name>
        <Place>italy</Place>
        <Salary>35 Lpa</Salary>
        <Cars>
            <Car_1>AUDI</Car_1>
            <Car_2>FERRARI</Car_2>
        </Cars>
    </Friend>
    <Friend id="5">
        <Name>soni</Name>
        <Place>espanol</Place>
        <Salary>38 Lpa</Salary>
        <Cars>
            <Car_1>BMW</Car_1>
            <Car_2>AUDI</Car_2>
        </Cars>
    </Friend>
    <Friend id="6">
        <Name>rose</Name>
        <Place>new york</Place>
        <Salary>38 Lpa</Salary>
        <Cars>
            <Car_1>AUDI</Car_1>
            <Car_2>Rolls Royce</Car_2>
        </Cars>
    </Friend>
    <Friend id="7">
        <Name>ranit</Name>
        <Place>chennai</Place>
        <Salary>4 Lpa</Salary>
        <Cars>
            <Car_1>NA</Car_1>
        </Cars>
    </Friend>
</FRIENDS>