从一个表中从重复的元素及其子元素 \ 属性中获取多个 XML 记录

Question

现在的算法使用... ，x.n.value ...[1] 仅检索第一条记录的方法。使用什么来检索所有现有元素的值(以及层次结构中的其他记录(更好？

主 XML 提取基于类似

SELECT..
CAST(decompress([Data]) AS NVARCHAR(MAX)
FROM.. 

然后去：

;WITH XMLNAMESPACES (DEFAULT 'urn://somenamespacename')
INSERT INTO  [DB].[dbo].[table] (
ID
,Att1
,Att2
,SubAtt1
,SubAtt2
,Lenght
,Neighbour
)
SELECT
@ID
,x.n.value('(@Att1)[1]', 'VARCHAR(100)')
,x.n.value('(@Att2)[1]', 'VARCHAR(100)')
,x.n.value('(*:Neighbours/*:SubBorder/@SubAtt1)[1]', 'VARCHAR(100)')
,x.n.value('(*:Neighbours/*:SubBorder/@SubAtt2)[1]', 'VARCHAR(100)')
,x.n.value('(*:Edge/*:Lenght)[1]', 'int')
,x.n.value('(*:Edge/*:Neighbour)[1]',  'VARCHAR(100)')
FROM @xml.nodes('/root/*:TOP/*:Border')  as x(n)

XML 示例

<root>
<TOP ID="1">
<Border Att1="BorderValue1" Att2="BorderValue2">
<Edge>
<Length>100</Length>
<Neighbours>
<Neighbour>alpha</Neighbour>
<SubBorder SubAtt1="SubValue1" SubAtt2="SubValue2" />
</Neighbours>
</Edge>
</Border>
<Border Att1="BorderValue3" Att1="BorderValue4">
<Edge>
<Length>300</Length>
<Neighbours>
<Neighbour>bravo</Neighbour>
<SubBorder SubAtt1="SubValue3" SubAtt2="SubValue4" />
</Neighbours>
</Edge>
</Border>
</TOP>
</root>

期望的输出

ID      Att1          Att2         SubAtt1    SubAtt2    Lenght    Neighbour       
1   BorderValue1 BorderValue2    SubValue1  SubValue2   100        alpha
1   BorderValue3 BorderValue4    SubValue3  SubValue4   300        bravo

Answer 1

您正在声明一个命名空间，但 XML 没有命名空间部分...
此外，还有一个双重属性"Att1"，这可能是一个拼写错误

但是，您似乎正在寻找.nodes()。此函数检索派生表中的重复元素：

您的 XML(不带命名空间(：

DECLARE @xml XML=
N'<root>
<TOP ID="1">
<Border Att1="BorderValue1" Att2="BorderValue2">
<Edge>
<Length>100</Length>
<Neighbours>
<Neighbour>alpha</Neighbour>
<SubBorder SubAtt1="SubValue1" SubAtt2="SubValue2" />
</Neighbours>
</Edge>
</Border>
<Border Att1="BorderValue3" Att2="BorderValue4">
<Edge>
<Length>300</Length>
<Neighbours>
<Neighbour>bravo</Neighbour>
<SubBorder SubAtt1="SubValue3" SubAtt2="SubValue4" />
</Neighbours>
</Edge>
</Border>
</TOP>
</root>';

--查询

SELECT @xml.value('(/root/TOP/@ID)[1]','int') AS ID
,brd.value('@Att1','nvarchar(max)') AS Att1
,brd.value('@Att2','nvarchar(max)') AS Att2
,brd.value('(Edge/Length/text())[1]','decimal(10,4)') AS [Length]
,nghbs.value('(Neighbour/text())[1]','nvarchar(max)') AS Border_Neighbour
,nghbs.value('(SubBorder/@SubAtt1)[1]','nvarchar(max)') AS Border_SubAtt1
,nghbs.value('(SubBorder/@SubAtt2)[1]','nvarchar(max)') AS Border_SubAtt2
FROM @xml.nodes('root/TOP/Border') ATU_2x_check(brd)
OUTER APPLY brd.nodes('Edge/Neighbours') B(nghbs);

简而言之，这个想法：

• 我们可以直接选择ID(不重复(
• 我们使用.nodes()来获取重复的<Border>元素
• 我们可以更深入地使用具有相对 XPath 的.nodes()来重复<Neighbours>
• 我们可以使用.value()从给定片段中读取值。