如何在SQL Server中合并两个XML CTE？ [英] How to Merge Two XML CTE's together in SQL Server?

问题描述

我有以下CTE，我正在尝试通过它们的公共refid密钥在Sql Server中组合XML1和XML2 CTE。

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

输出应显示每个refid如下：

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

如果有人能告诉我如何使用XML1和XML2编写此查询，那将会非常棒。而且我相信这也将极大地帮助其他人在SQL中进行转换。

It would be so great if anyone can show me how to write this query using XML1 and XML2. And I'm sure this will greatly help others doing transform in SQL as well.

谢谢，

Student_XML

Student_XML

推荐答案

你真正的问题是什么？在形成XML之前加入数据肯定更容易。但我能理解这是你现实世界问题的综合版本。

What is your real problem? It certainly seems easier to join the data before you form the XML. But I can understand that this a syntesized version of your real-world problem.

这篇关于如何在SQL Server中合并两个XML CTE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！