問題描述

我試圖在 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);

以上查詢的結(jié)果:

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

更新:獲取值

正如您所發(fā)現(xiàn)的，上述方法不能向后移動到 .試試這個:

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