1. 首页
  2. 问题
  3. XML到SQL的问题-SQL Server
小编典典

XML到SQL的问题-SQL Server

sql

我正在尝试获取sql脚本中xml的特定元素的位置，但是我正在获取特定元素的所有位置的详细信息，因为我仅在查找特定级别。

这是代码：

declare @xmlVar xml ='
<A specVersion="2.09">
  <B id="1" type="Regular">
    <C>
      <D>
        <E actioncode="A" date="06/13/2018 09:20" />
        <E  actioncode="B" date="06/13/2018 09:20" />
      </D>
      <D>
        <E actioncode="C" date="06/13/2018 09:20" />
      </D>
    </C>
  </B>

  <B id="2" type="Regular">
    <C>
      <D>
        <E  actioncode="D" date="06/13/2018 09:20" />
      </D>
    </C>
    <F>
        <D>
            <E  actioncode="F" date="06/13/2018 09:20" />
        </D>
    </F>
  </B>
</A>' ;

WITH Tally(Nmbr) AS
(
    SELECT TOP (SELECT @xmlVar.value(N'count(//D)','int'))
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM master..spt_values --just a pre-filled table with many rows
)
SELECT Nmbr
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM Tally
OUTER APPLY(SELECT  @xmlVar.query(N'//D').query(N'/D[sql:column("Nmbr")]')) AS B(ds)
OUTER APPLY ds.nodes(N'D/E') AS C(e);

以上查询结果：

1   A
1   B
2   C
3   D
4   F

预期产量：

1 A
1 B
2 C
3 D

我正在寻找路径A-> B-> C-> D中所有D的位置，而不是其他路径中的D的位置。


阅读 215

收藏
2021-05-16

共1个答案

小编典典

好的，这似乎是对您之前的问题的一个相当容易的更改。您可以使用相同的逻辑，但是将其简化为您想要查看的路径：

WITH Tally(Nmbr) AS
(
    SELECT TOP (SELECT @xmlVar.value(N'count(/A/B/C/D)','int'))
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM master..spt_values --just a pre-filled table with many rows
)
SELECT Nmbr
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM Tally
OUTER APPLY(SELECT  @xmlVar.query(N'/A/B/C/D').query(N'/D[sql:column("Nmbr")]')) AS B(ds)
OUTER APPLY ds.nodes(N'D/E') AS C(e);

更新：获取<B id="x">价值

如您所知，上述方法无法向后移动到<B>。试试这个：

WITH AllBs AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS B_position
          ,b.value('@id','int') AS B_id
          ,b.query('.') AS B_node
    FROM @xmlVar.nodes('/A/B') AS A(b)
)
,AllDs As
(
    SELECT AllBs.*
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS D_position
          ,d.query('.') AS D_node 
    FROM AllBs 
    OUTER APPLY B_node.nodes('B/C/D') AS A(d)
)
SELECT AllDs.*
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM AllDs
OUTER APPLY D_node.nodes('D/E') AS A(e);

正如我在第一个答案中所写的那样，排序顺序存在很小的风险…但是使用 理货表的
方法无法获取第3次出现的情况，因为其中的位置[sql:column()]会与任何父级一起重新编号。这意味着：3rd<D>是2nd中的1st
<B>。这就是为什么我.query()在两者之间放置…

2021-05-16