sql - 查询具有一对多关系的 XML 数据

Question

我无法在 SQL Server 中进行查询以将以下 XML 数据转换为我想要的格式。 header 和详细信息元素之间存在一对多关系。我希望每个详细信息元素的输出都有一行。

示例代码

declare @xmldata xml;
set @xmldata =
'
<DATA>
    <HEADER>
        <HEADER_ID>1</HEADER_ID>
        <OPERATOR>BOB</OPERATOR>
        <TEST>RANDOM</TEST>
        <DATE>1/1/2018</DATE>
        <PARAMETER>XYZ</PARAMETER>
        <DETAILS>
            <DETAIL>
                <VALUE>5</VALUE>
                <RESULT>PASS</RESULT>
            </DETAIL>
            <DETAIL>
                <VALUE>10</VALUE>
                <RESULT>FAIL</RESULT>
            </DETAIL>
        </DETAILS>
    </HEADER>
    <HEADER>
        <HEADER_ID>2</HEADER_ID>
        <OPERATOR>Joe</OPERATOR>
        <TEST>RANDOM</TEST>
        <DATE>1/2/2018</DATE>
        <PARAMETER>XYZ</PARAMETER>
        <DETAILS>
            <DETAIL>
                <VALUE>1</VALUE>
                <RESULT>PASS</RESULT>
            </DETAIL>
            <DETAIL>
                <VALUE>23</VALUE>
                <RESULT>FAIL</RESULT>
            </DETAIL>
            <DETAIL>
                <VALUE>56</VALUE>
                <RESULT>FAIL</RESULT>
            </DETAIL>
        </DETAILS>
    </HEADER>
</DATA>'

select
x.y.value('HEADER_ID[1]','int') as [HEADER_ID],
x.y.value('OPERATOR[1]','char(10)') as [OPERATOR],
x.y.value('TEST[1]','char(10)') as [TEST],
x.y.value('DATE[1]','date') as [DATE],
x.y.query('./DETAILS/DETAIL/VALUE') as [VALUE],
x.y.query('./DETAILS/DETAIL/RESULT') as [RESULT]
from @xmlData.nodes('/DATA/HEADER') as x(y)

Current Result

Desired Result

Answer 1

使用CROSS APPLY 在DETAIL 元素级别分解 XML:

select
    x.y.value('HEADER_ID[1]','int') as [HEADER_ID],
    x.y.value('OPERATOR[1]','char(10)') as [OPERATOR],   
    x.y.value('TEST[1]','char(10)') as [TEST],   
    x.y.value('DATE[1]','date') as [DATE],
    t.c.value('VALUE[1]','int') as [VALUE],
    t.c.value('RESULT[1]','varchar(10)') as [RESULT]
from @xmlData.nodes('/DATA/HEADER') as x(y)
    cross apply x.y.nodes('DETAILS/DETAIL') as t(c)

演示:http://www.sqlfiddle.com/#!18/9eecb/35798