How to get hierarchical xml based of multiple tables xml columns

Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..