I have the following CTE's, and I'm trying to combine XML1 and XML2 CTE's in Sql Server by their common refid key.

;With TBL1_Fruits As 
(Select '1' as refid,
        'Apples' as fruits,
        'Red' as color,
        'Macintosh' as  category union
 Select  '2' as refid,
        'Oranges' as fruits,
        'Orange' as color,
        'Bergamot' as category union
 Select '3' as refid,
        'Bananas' as fruits,
        'Yellow' as color,
        'Cavendish' as category)

,TBL1_Export As
(Select 'X1234' as ShipID,
        'USA' as Country,
        'FarmersToYou' as Brand,
        '1' as refid union
 Select 'Y1234' as ShipID,
        'Costa_Rica' as Country,
        'Chiquita' as Brand,
        '3' as refid union
 Select 'Z1234' as ShipID,
        'USA' as Country,
        'Hawaiia_Sun_Tropical' as Brand,
        '2' as refid)

,TBL2_Price As
(Select '1' as refid,
        '4.50' as price,
        'lb' as unit union
 Select '2' as refid,
        '6.50' as price,
        'lb' as unit union
 Select '3' as refid,
        '2.50' as price,
        'lb' as unit)
,TBL2_Costs As
(Select '1' as refid,
        '1.50' as shipping_cost,
        '3.00' as profit,
        'lb' as unit_rev union
 Select '2' as refid,
        '3.00' as shipping_cost,
    '3.50' as profit,
    'lb' as unit_rev union
 Select '3' as refid,
        '0.50' as shipping_cost,
    '2.00' as profit,
    'lb' as unit_rev)

,XML1 As (Select * From (
Select refid, fruits, color, category,
 (Select Distinct ShipID, Country, Brand from TBL1_Export
   Where TBL1_Export.refid = TBL1_Fruits.refid
   FOR XML PATH ('FruitAttributes'),TYPE)
  From (Select Distinct refid, fruits, color, category From TBL1_Fruits) 
  TBL1_Fruits
  FOR XML PATH (''), ROOT('FruitInfo'), TYPE) As x(Fruits))

,XML2 As 
(Select * From (
Select refid, price, unit,
 (Select Distinct shipping_cost, profit, unit_rev from TBL2_Costs
   Where TBL2_Price.refid = TBL2_Costs.refid
   FOR XML PATH ('FruitProfit'),TYPE)
  From (Select Distinct refid, price, unit From TBL2_Price) TBL2_Price
  FOR XML PATH (''), ROOT('FruitRevenue'), TYPE) As c(Fruits))

The final output should look like the following (using 1 refid as an example):

<FruitInfo>
  <refid>1</refid>
  <fruits>Apples</fruits>
  <color>Red</color>
  <category>Macintosh</category>
  <FruitAttributes>
    <ShipID>X1234</ShipID>
    <Country>USA</Country>
    <Brand>FarmersToYou</Brand>
  </FruitAttributes>
</FruitInfo>
<FruitRevenue>
  <refid>1</refid>
  <price>4.50</price>
  <unit>lb</unit>
  <FruitProfit>
    <shipping_cost>1.50</shipping_cost>
    <profit>3.00</profit>
    <unit_rev>lb</unit_rev>
  </FruitProfit>
</FruitRevenue>

I'm just wondering if anyone can help me formulate the SQL query to join XML1 and XML2 to produce the above output using refid.

Thank you so much,

student