問題描述

在上一個問題中，我想知道如何使用 SQL 根據標識符JOIN 不同的 XML 元素.我得到了幾個不錯的解決方案，你可以看到 這里.

In a previous question I wanted to know how I can use SQL to JOIN different XML elements based on an identifier. I was provided with several nice solutions as you can see here.

現在我正在根據我的實際數據調整此解決方案.不幸的是，我偶然發現了我在鏈接問題中提供的最小可行示例中不存在的新障礙.在我的實際數據中，我還有幾個同名的子元素.請參閱以下示例數據中的元素 .

Now I am in the process of adapting this solution to my actual data. Unfortunately I stumbled upon a new obstacle that was not present in the minimum viable example I provided in the linked question. In my actual data I also have several child elements of the same name. See the element <subElement> in the following example data.

<xml>
    <dataSetData>
        <text>ABC</text>
    </dataSetData>
    <generalData>
        <id>123</id>
        <text>text data</text>
        <subElement>
            <subData>sub example data AAA</subData>
        </subElement>
        <subElement>
            <subData>sub example data BBB</subData>
        </subElement>
    </generalData>
    <generalData>
        <id>456</id>
        <text>text data 2</text>
        <subElement>
            <subData>sub example data CCC</subData>
        </subElement>
    </generalData>
    <specialData>
        <id>123</id>
        <text>special data text</text>
    </specialData>
    <specialData>
        <id>456</id>
        <text>special data text 2</text>
    </specialData>
</xml>

預期結果應如下所示.

DataSetData | GeneralDataID | GeneralDataText | subData              | SpecialDataTest
ABC         | 123           | text data       | sub example data AAA | special data text
ABC         | 123           | text data       | sub example data BBB | special data text
ABC         | 456           | text data  2    | sub example data CCC | special data text 2

目前的解決方案(不考慮數據)如下(取自這里):

The current solution (without considering the <subElement> data) is as follows (taken from here):

SELECT TheXml.value('(/xml/dataSetData/text/text())[1]', 'VARCHAR(20)') AS DataSetData
     ,B.*
    , sp.value('(id/text())[1]', 'INT') AS SpecialDataID 
    , sp.value('(text/text())[1]', 'VARCHAR(30)') AS SpecialDataTest
INTO dbo.TestResult4
FROM dbo.TestXml
CROSS APPLY TheXml.nodes('/xml/generalData') AS A(g)
CROSS APPLY(SELECT g.value('(id/text())[1]', 'INT') AS GeneralDataID 
                 , g.value('(text/text())[1]', 'VARCHAR(30)') AS GeneralDataText) B
OUTER APPLY TheXml.nodes('/xml/specialData[id=sql:column("B.GeneralDataID")]') AS special(sp);

推薦答案

好的，這很簡單...

SELECT TheXml.value('(/xml/dataSetData/text/text())[1]', 'VARCHAR(20)') AS DataSetData
    , B.*
    , se.value('(subData/text())[1]','varchar(100)') AS SubData 
    , sp.value('(id/text())[1]', 'INT') AS SpecialDataID 
    , sp.value('(text/text())[1]', 'VARCHAR(30)') AS SpecialDataTest
FROM dbo.TestXml
CROSS APPLY TheXml.nodes('/xml/generalData') AS A(g)
CROSS APPLY(SELECT g.value('(id/text())[1]', 'INT') AS GeneralDataID 
                 , g.value('(text/text())[1]', 'VARCHAR(30)') AS GeneralDataText) B
OUTER APPLY A.g.nodes('subElement') C(se)
OUTER APPLY TheXml.nodes('/xml/specialData[id=sql:column("B.GeneralDataID")]') AS D(sp);

這篇關于SQL Server XML 查詢:查詢多個同名子元素的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！