select Table1.AssetObjID as "@AssetObjID",

Table1.Asset_ID as "@Asset_ID",

Table1.FromLR as "@FromLR",

Table1.AssetType + ', ' + Table1.StreetName + ', ' + Table1.FromMunicNo as "@FirstRow",

(

select Table2.ACIObjID as "@ACIObjID",

Table2.PAssetObjID as "@PAssetObjID",

Table2.Feature_ID + ', ' + Table2.FeatureName AS "@ChildOfFirstRow",

(

select Table3.ITMObjID as "@ITMObjID",

Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"

from Table3

where Table1.AssetObjID = Table3.AssetObjID and

Table2.ACIObjID = Table3.ACIObjID

for xml path('Table3'), type

)

from Table2

where Table1.AssetObjID = Table2.PAssetObjID

for xml path('Table2'), type

),

(

select Table3.ITMObjID as "@ITMObjID",

Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"

from Table3

where Table1.AssetObjID = Table3.AssetObjID and

Table2.ACIObjID <> Table3.ACIObjID

for xml path('Table3'), type

)

from Table1

where Table1.AssetType = 'xxxx'

for xml path('Table1'), root('xml')