数据中建立表 CREATE TABLE [Team]( [TeamID] [int] IDENTITY(1,1) NOT NULL, [TeamDoc] [xml] NOT NULL DEFAULT ('<Team />') ) 插入资料 insert [Team]([TeamDoc]) values(' <Team name="Braves"> <Players> <Pitcher name="John Smoltz" role="Closer" /> <Pitcher name="Russ Ortiz" role="Starter" /> <ThirdBase name="Chipper Jones" role="Starter" bats="switch" /> </Players> </Team>') 1 .根据name动态查询记录,可以借助ADO.net 的 SqlCommand.CommandText = " select TeamDoc.query('Team/Players/Pitcher[@name=sql:variable(""@paramvalue"")]') from Team "; SqlCommand.AddParamter("@paramvalue","John Smoltz"); 2.SQL直接实现: declare @sql nvarchar(max) set @sql = N' select TeamDoc.query(''Team/Players/Pitcher[@name=sql:variable("@paramvalue")]'') from Team ' execute sp_executesql @sql,N'@paramvalue nvarchar(max)',@paramvalue=N'John Smoltz' //------------------------------------------------------------------------------------------------------ 参考别人: 1. 使用表栏位做参数 DECLARE @names xml SET @names = '<root> <row id="1">aa</row> <row id="2">bb</row> </root>' SELECT A.id, [name] = @names.value('(/root/row[@id=sql:column("id")])[1]', 'varchar(10)') FROM( SELECT id = 1 UNION ALL SELECT id = 2 )A 2.使用SQL 变量作为参数 DECLARE @a xml SET @a = '<root> <row id="1">aa</row> <row id="2">bb</row> </root>' DECLARE @id nvarchar(50) SET @id = '2' SELECT @a.value('(/root/row[@id=sql:variable("@id")])[1]', 'varchar(10)') |