Trying to Build TreeView Query in T-SQL

Question

I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2.

I would like to build a query that I can use to populate a TreeView control with the data from Table3 displaying either under Table1 if it only has a parent in Table1 and not Table2, or Table2 if it has parent records in both Table1 and Table2.

Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.

How can I output that in a query for a TreeView? I am assuming that I will have to program the output in C#, with 3 FOR loops. In the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of the TreeView, but I am having a problem building a query in SQL.

The query below shows all Parent, then child then grandchild, which is working, but what is desired is at times child takes place of a father.

DECLARE @x AS XML
SET @x =
(
    SELECT DISTINCT 
        Table1.AssetSysID
        , Table1.Asset_ID
        , Table1.FromLR
        , Table1.Asset_ID + ', ' + Table1.[Desc2] AS GarndFather
        , Table2.ACISysID
        , Table2.PAssetSysID
        , Table2.FeatureName + ', ' + Table2.[DESC] AS Father
        , Table3.ITMSysID
        , Table3.Item_ID + ',' + Table3.[DESC] AS Child
    FROM Table1 
        LEFT OUTER JOIN Table2 ON Table1.AssetSysID = Table2.PAssetSysID 
        LEFT OUTER JOIN Table3 ON Table1.AssetSysID = Table3.AssetSysID 
                                AND Table2.ACISysID = Table3.ACISysID
    WHERE (Table1.AssetType = @AssetType)
    FOR XML AUTO,root('xml')
);

I've modified the code above to reflect some changes:

DECLARE @x AS XML
SET @x =
(
    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')
);

Answer 1

Got an Idea from Mikael Eriksson and a help from a team lead @ work

  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')

So It goes something like this:

<Table1 atrributes......... FirstRow=".........."/>
<Table2 atrributes......... ChildOfFirstRow="..........">
<Table3 atrributes......... GrandChildOfFirstRow=".........."/>
</Table2>
<Table3 atrributes......... GrandChildOfFirstRow="This GrandChild took the importance as equal to ChildOfFirstRow"/>