問題描述

我有兩個單獨的表 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')

這篇關于結合來自 T-SQL 的 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！