問題描述
我試圖在 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.
這篇關于XML to SQL 問題 - 如何獲取元素的位置的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！