问题描述
我试图在 sql 中获取 XML 节点元素的位置,但它没有给我正确的结果,@Shungo 建议使用该方法
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);
上面的查询给出了错误的结果:
The above query is giving wrong results :
1 A 1 B 1 D 2 C 3 NULL
预期输出:
1 A 1 B 2 C 3 D
任何帮助将不胜感激.谢谢.
Any help would be appreciated . Thanks.
推荐答案
嗯,这和之前的完全不一样了...
Well, this is quite different to the one before...
看起来,您希望将 <D> 节点放在任何位置.这有帮助吗?
As it looks, you want to take <D> nodes whereever they are placed. Does this help?
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);
第一个应用程序将使用深度搜索(使用//D)来获取仅包含这些节点的派生 XML.其余的和以前一样.
The first apply will use the deep search (with //D) to get a derived XML including these nodes only. The rest is rather the same as before.