XML 到 SQL 的问题 - SQL Server

2021-09-10 00:00:00 xml sql tsql sql-server

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

I am trying to get position of a specific element of xml in sql script , but I am getting details for all the positions of a specific element , where as I am looking for a particular level only.

这是代码:

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 的位置，而不是其他路径中的位置.

I am looking for positions of all D which comes in path A->B->C->D not the one's which come in other path.

推荐答案

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

Okay, this seems to be a rather easy change to your previous question. You can use the same logic, but reduce it to the path you want to see:

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);

更新:获取值

正如您所发现的，上述方法不能向后移动到 .试试这个:

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);

正如我在第一个答案中所写的，排序顺序存在很小的风险......但是使用 tally 表 的方法无法获取第三次出现，因为 [sql:column()] 与任何父项重新编号.这意味着:第三个 是第二个 中的第一个.这就是为什么我将 .query() 放在中间......

As written in my first answer, there's a tiny risk about the sort order... But the approach with the tally table cannot fetch the 3rd occurance, because the position within [sql:column()] is re-numbered with any parent. That means: The 3rd <D> is the 1st in the 2nd <B>. That's why I placed the .query() in between...

相关文章