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