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

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

# 星寂 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)

你的看法呢?

標題 (必要的) 

姓名 (必要的) 

你的URL (選擇性的)

意見 (必要的) 

Remember Me?

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

CAPTCHA / 驗證碼 :

5 + 3 =

搜尋