Loading the data from a table to the XML TARGET

The data looks like this in the source table :
ID NAME ADD1 STREET1 CITY1 COUNTRY1 ADD2 STREET2 CITY2 COUNTRY2
1 SUDHEER 216 SHIPRA GZB INDIA C-701 JAIPURIA GZB INDIA2 ABHISHEK 275 DWARKA DELHI INDIA

The output structure should be

ID NAME ADDRESS

In the ADDRESS you will find the ADD,STREET,CITY (AS MANY AS ARE THEY Eg : ADD1,ADD2 in the above eg : It can be ADD1,ADD2,ADD3,ADD4)

Sol :

FLOW :

SOURCE TABLE --- QUERY1--PIVOT--- QUERY2---QUERY3--- QUERY4---XMLTARGET

1)QUERY1--* Simply Extracting the data from the Source Table.

2)PIVOT---* Converting the Columns to rows

4 PIVOTAL SETS

1st Set---* ADD1, ADD2
2nd Set---* STREET1,STREET2
3rd Set---* CITY1,CITY2
4th Set---* COUNTRY1,COUNTRY2

Name the DATA FIELD COLUMNS as ADD,STREET,CITY,COUNTRY

3)QUERY3--* Make the XML Structure you are in need of

4)QUERY4--*For the Parent tree select DISTINCT ROWS option

In order to restrict DUPLICATE data.

FOR THE ADDRESS heirarchy place the following condition in the
where condition.

(((((Xml_Struct.ID = Xml_Struct.Pivot.ID) AND
(Xml_Struct.Pivot.ADD_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.STRT_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CITY_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CNTRY_DATA IS NOT NULL ))

In order to restrict NULL data

In the Group By tab place the following :
(((((Xml_Struct.ID = Xml_Struct.Pivot.ID) AND
(Xml_Struct.Pivot.ADD_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.STRT_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CITY_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CNTRY_DATA IS NOT NULL ))

In order to restrict DUPLICATE data.

5)QUERY5---*Construct the XML structure for NRDM column