时间戳

首页 > 解决方案 > 从重复元素及其子元素\属性中获取一张表中的多条XML记录

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

问题描述

本算法使用 ... ,xnvalue ... [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

标签： sqlsql-serverxml

解决方案

您正在声明一个命名空间，但 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重复<Neighbours>使用.nodes()
  • 我们可以使用.value()从给定片段中读取值。

推荐阅读