问题描述
我有两个单独的表 TVs 和 Receiver,我正在使用 FOR XML PATH 命令来构建 XML.我的问题是我想将 TV XML Build 的输出与我的 Receiver XML Build 结合起来创建一个 XML 输出.
I have two separate tables TVs and Receivers that I am using the FOR XML PATH commands to build XML off of. My issue is that I want to combine the output of my TV XML Build with my Receiver XML Build to create one XML output.
所以我会有这样的东西(它允许我在 FilterData 根中保持电视和接收器标签分开):
So I would have something like this(Which allows me to keep the TVs and Receivers Tags Separate within the FilterData Root):
<FilterData> <TVs> <TV> <Type>LCD</Type> <Brand>Samsung</Brand> </TV> <TV> <Type>LCD</Type> <Brand>Panasonic</Brand> </TV> </TVs> <Receivers> <Receiver> <Type>Surround 7.1</Type> <Brand>Onkyo</Brand> </Receiver> <Receiver> <Type>Surround 7.1</Type> <Brand>Denon</Brand> </Receiver> </Receivers> </FilterData>
问题是当我构建查询以输出这个 XML
The problem is that when I build my queries to output this XML
Select Type ,Brand From dbo.TVs FOR XML PATH('TV'),ROOT('TVS') TYPE
和
Select Type ,Brand From dbo.Receivers FOR XML PATH('Receiver'),ROOT('Receivers') TYPE
我不知道如何将这些组合起来看起来像示例:
I am not sure how to combine these to look like the example:
<FilterData> <TVs> <TV> <Type>LCD</Type> <Brand>Samsung</Brand> </TV> <TV> <Type>LCD</Type> <Brand>Panasonic</Brand> </TV> </TVs> <Receivers> <Receiver> <Type>Surround 7.1</Type> <Brand>Onkyo</Brand> </Receiver> <Receiver> <Type>Surround 7.1</Type> <Brand>Denon</Brand> </Receiver> </Receivers>
推荐答案
使用:
SELECT (SELECT t.type, t.brand FROM dbo.TVs t FOR XML PATH('tv'), ROOT('tvs'), ELEMENTS, TYPE), (SELECT r.type, r.brand FROM dbo.Receivers r FOR XML PATH('receiver'), ROOT('receivers'), ELEMENTS, TYPE) FOR XML PATH('filterdata')
测试使用:
WITH tvs AS ( SELECT 'LCD' AS type, 'Samsung' AS brand UNION ALL SELECT 'LCD' AS type, 'Panasonic' AS brand), receivers AS ( SELECT 'Surround 7.1' AS type, 'Onkyo' AS brand UNION ALL SELECT 'Surround 7.1', 'Denon') SELECT (SELECT t.type, t.brand FROM tvs t FOR XML PATH('tv'), ROOT('tvs'), ELEMENTS, TYPE), (SELECT r.type, r.brand FROM receivers r FOR XML PATH('receiver'), ROOT('receivers'), ELEMENTS, TYPE) FOR XML PATH('filterdata')