更新时间:2023-02-03 13:37:54
嗯,这和之前的完全不一样了...
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.