今天處理到一個需求,要在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

Comments

# by 星寂

我來提供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)

Post a comment