分解 xml 列

Question

我有一个 XML 列，其中包含这样的 XML：

<Set>
    <Element>
        <ID>
            1
        </ID>
<List>
    <ListElement>
        <Part1>
            ListElement 1
        </Part1>
    </ListElement>
    <ListElement>
        <Part1>
            ListElement2
        </Part1>
    </ListElement>
</List>
    </Element>
    <Element>
        <ID>
            2
        </ID>
<List>
    <ListElement>
        <Part1>
            ListElement3
        </Part1>
    </ListElement>
    <ListElement>
        <Part1>
            ListElement4
        </Part1>
    </ListElement>
</List>
    </Element>
</Set>

我想将其分解为包含以下内容的关系表：

ID, ListElement
1, ListElement1
1, ListElement2
2, ListElement3
2, ListElement4

我能够使用类似这样的内容获取零件的内容：

select      
    List.value('(Part1/text())[1]', 'varchar(max)') as test
from 
    Table CROSS APPLY 
      xml.nodes('// Element/List/ListElement') AS List(List)

但我还没有实现保留“外国” key'（ID 值）。

谢谢。

最好的祝愿，

克里斯蒂安

Answer 1

尝试这个而不是您的查询：

SELECT      
    List.value('(../../ID)[1]', 'int') AS 'ID',
    List.value('(Part1/text())[1]', 'varchar(max)') as test
FROM
    dbo.Table 
CROSS APPLY 
    xml.nodes('/Set/Element/List/ListElement') AS List(List)

再次更新 - 现在回到只有一个 CROSS APPLY，使用 XPath 中的 ../../ID 回到祖父母的“ID”元素。还消除了 //Element XPath，转而使用 /Set/Element（//Element xpath 是出了名的慢）

Answer 2

如果有人想使用 XQuery 执行此操作：

<Table>
   {for $listElement in $table//ListElement
    return
      <Row>
        {$listElement/../../ID}
        {$listElement/Part1}
      </Row>    
   }
</Table>

其中 $table 是原始 XML

返回

<Table>
    <Row>
        <ID> 1 </ID>
        <Part1> ListElement 1 </Part1>
    </Row>
    <Row>
        <ID> 1 </ID>
        <Part1> ListElement2 </Part1>
    </Row>
    <Row>
        <ID> 2 </ID>
        <Part1> ListElement3 </Part1>
    </Row>
    <Row>
        <ID> 2 </ID>
        <Part1> ListElement4 </Part1>
    </Row>
</Table>