gpt4 book ai didi

tsql - 如何将 CTE 与 FOR XML 子句结合使用?

转载 作者:行者123 更新时间:2023-12-01 04:14:06 27 4
gpt4 key购买 nike

我正在尝试生成一些具有各种嵌套级别的 XML,并且冒着过度简化的风险,输出的 XML 将采用松散的格式:

<invoice number="1">
<charge code="foo" rate="123.00">
<surcharge amount="10%" />
</charge>
<charge code="bar" />
</invoice>

我为此继承的数据库模式恰好将费用存储在不同的表中,这意味着附加费的存储方式取决于费用来自何处的表。

鉴于您 cannot use UNION s with FOR XML ,我做了一些 UNION在 CTE 中进行,因此类似于以下内容:
WITH Charges ( [@code], [@rate], surcharge, InvoiceId ) AS (
SELECT code AS [@Code], amount AS [@rate], NULL as surcharge, InvoiceId
FROM item.charges
UNION ALL
SELECT
code AS [@Code],
amount AS [@rate],
(
SELECT amount AS [@amount]
FROM order.surcharges os
WHERE oc.ChargeId = os.ChargeId
FOR XML PATH('surcharge'), TYPE
),
InvoiceId
FROM order.charges oc
)
SELECT
Number AS [@number],
(
SELECT
[@code],
[@rate],
surcharge
FROM Charges
WHERE Charges.InvoiceId = i.InvoiceId
)
FROM Invoices i
FOR XML PATH( 'invoice' ), TYPE

现在,这非常接近,给出(注意嵌套的 <surcharge> ):
<invoice number="1">
<charge code="foo" rate="123.00">
<surcharge>
<surcharge amount="10%" />
</surcharge>
</charge>
<charge code="bar" />
</invoice>

但是我需要找到一种方法来获取最终查询以包含要被视为元素内容而不是新元素的 XML 列的值。这是可能的,还是我需要采取新的方法?

最佳答案

您有一个返回多行(@charge、@rate 和 XML 类型)的列查询。我希望您发布的查询会给出错误:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.



然而,这很容易通过将查询移动到 outer apply 来解决。 .删除双 surcharge元素,您可以将 XML 列名称尽可能移到底部,例如:
;WITH Charges (code, rate, surcharge, InvoiceId) AS 
(
SELECT code, amount, NULL, InvoiceId
FROM @charges
UNION ALL
SELECT code
, amount
, (
SELECT amount AS [@amount]
FROM @surcharges os
WHERE oc.ChargeId = os.ChargeId
FOR XML PATH('surcharge'), TYPE
)
, InvoiceId
FROM @charges oc
)
SELECT Number AS [@number]
, c.code as [charge/@code]
, c.rate as [charge/@rate]
, c.surcharge as [charge]
FROM @Invoices i
outer apply
(
SELECT code
, rate
, surcharge
FROM Charges
WHERE Charges.InvoiceId = i.InvoiceId
) c
WHERE i.InvoiceID = 1
FOR XML PATH( 'invoice' ), TYPE

这将打印,例如:
<invoice number="1">
<charge code="1" rate="1" />
</invoice>
<invoice number="1">
<charge code="1" rate="1">
<surcharge amount="1" />
</charge>
</invoice>

第一个元素来自并集的顶部,其中 surcharge = null .

关于tsql - 如何将 CTE 与 FOR XML 子句结合使用?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4648713/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com