CODE-在SQL Server XQuery中使用欄位或變數值當條件

今天處理到一個需求,要在SQL 2005中用Table1 JOIN Table2,Table2中有個XML欄位,裡面有多筆資料,JOIN時要用Table1的某個欄位當條件在XML中挑出特定XmlNode。

聽起來很模糊對吧? 我用來實例來比喻,假設有個Player資料表跟Team資料表,Team資料表中有個MemberListXml欄位,其中有該球隊成員的相關資料。我今天要用Player資料表去JOIN Team資料表,先找出Player所屬球隊,再從該球隊MemberListXml中用XPath語法調出該名球員的資料。

XQuery允許我們在SQL Server中使用熟悉的XPath,由於PlayerNo是變數,直覺上大家會想到動態組裝XPath字串,例如: query('/Members/Player[@PlayerNo = "' + Player.PlayerNo + '"]),但此時SQL會潑你一盆冷水,告訴你query()只能接受預先寫死的字串!! 怎麼辦?

請看示範:

DECLARE @Team TABLE (TeamNo INT, MemberListXml XML);
DECLARE @Player TABLE (PlayerNo INT, TeamNo INT, PlayerName VARCHAR(16));
INSERT INTO @Team VALUES(1, '<Members>
<Player PlayerNo="1" Score="5" />        
<Player PlayerNo="2" Rank="4" />
<Player PlayerNo="3" Position="Pitcher" />
</Members>');
INSERT INTO @Player VALUES(1, 1, 'Jeffrey');
INSERT INTO @Player VALUES(2, 1, 'Darkthread');
INSERT INTO @Player VALUES(3, 1, 'Wang');
--如果要從Team.MemberListXml中挑出Player的資料應如何JOIN
--直覺上會寫成
SELECT P.PlayerNo, P.PlayerName,
       T.MemberListXml.query(
       '/Members/Player[@PlayerNo = "' + P.PlayerNo + '"]'
       ) 
FROM @Player P JOIN @Team T ON P.TeamNo = T.TeamNo
--很不幸地,一執行就會得到以下錯誤
--The argument 1 of the xml data type method "query" must be a string literal.
 
--別怕!! 天無絕人之路,向sql:column("ColName")尋求救贖吧!
--另外,還有姐妹品 sql:variable("@varName")
SELECT P.PlayerNo, P.PlayerName,
       T.MemberListXml.query(
       '/Members/Player[@PlayerNo = sql:column("P.PlayerNo")]'
       ) 
FROM @Player P JOIN @Team T ON P.TeamNo = T.TeamNo
歡迎推文分享:
Published 08 January 2010 08:03 PM 由 Jeffrey
Filed under: , ,
Views: 10,198



意見

# 星寂 said on 13 January, 2010 04:47 AM

我來提供variable的範例XD

SELECT @TechName = N.value('@name', 'nvarchar(max)')

FROM @doc.nodes('/node/node[.//node/@id=(sql:variable("@NodeID")) or @id=(sql:variable("@NodeID"))]') V(N)

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<January 2010>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication