XML to SQL 问题 - 如何获取元素的位置

I am trying to get the position of an element of an XML node in sql , and it is not giving me correct results , the approach was suggested by @Shungo

这是代码:

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>
  </B>
</A>' ;
    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[sql:column("Nmbr")]')) AS A(ds)
OUTER APPLY ds.nodes(N'D/E') AS B(e);

上面的查询给出了错误的结果:

1   A
1   B
1   D
2   C
3   NULL

1  A
1  B
2  C
3  D

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