Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Merge XML CTE's in SQL Server Using Common Refid Key

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 78
    Answer it

    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

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: