为了解决在大数据查询时不使用 in 操作符，所以使用下面的方案替换 in 的使用

--------------------------------------------------@H_301_3@

--1.优化前后代码对比

优化前的代码：

select * from orderinfo where orderid in (1,2,3,4,5,........,n);

优化后的代码：

declare @xmlDoc xml;@H_301_3@ set @xmlDoc='@H_301_3@ <orders title="订单号">@H_301_3@ <id>1111</id>@H_301_3@ <id>2222</id>@H_301_3@ <id>3333</id>@H_301_3@ <id>4444</id>@H_301_3@ </orders>';@H_301_3@ select * from orderinfo@H_301_3@ where exists@H_301_3@ (@H_301_3@ select 1 from @xmlDoc.nodes('/orders/id') as T(c) @H_301_3@ where T.c.value('text()[1]','int')= orderinfo.OrderID@H_301_3@ );@H_301_3@ @H_301_3@

--------------------------------------------------

--2.其它基础的 xquery 使用

declare @xmlDoc xml; set @xmlDoc=' <orders title="订单号"> <id>862875</id> <id>862874</id> <id>862873</id> <id>862872</id> </orders>'; --查询所有Id节点 select @xmlDoc.query('/orders/id') c1 --查询第一个Id节点值,@xmlDoc.value('(/orders/id)[1]','nvarchar(max)') AS c2 --查询最后一个Id节点值,@xmlDoc.value('(/orders/id)[last()]','nvarchar(max)') AS c3 --查找属性,@xmlDoc.value('(/orders/@title)[1]','nvarchar(max)') AS c4 --xml 与表合并查询1（推荐） select * from orderinfo where exists ( select 1 from @xmlDoc.nodes('/orders/id') as T(c) where T.c.value('text()[1]','int')= orderinfo.OrderID ); --xml 与表合并查询（和in效果一样） select * from orderinfo where OrderID in ( select T.c.value('text()[1]','int') from @xmlDoc.nodes('/orders/id') as T(c) ); ---------------------------------- --3. ADO.NET 中执行xml查询 DataTable dt = new DataTable(); using (sqlConnection conn = new sqlConnection(connectionString)) { string xml = @" <orders title="订单号"> <id>862875</id> <id>862874</id> <id>862873</id> <id>862872</id> </orders> "; sqlCommand comm = conn.CreateCommand(); comm.CommandText = @" select * from orderinfo where exists ( select 1 from @xmlDoc.nodes('/orders/id') as T(c) where T.c.value('text()[1]','int')= orderinfo.OrderID );"; comm.Parameters.Add(new sqlParameter("@xml",sqlDbType.Xml) { Value = xml }); using (sqlDataAdapter adapter = new sqlDataAdapter(comm)) { adapter.SelectCommand = comm; adapter.Fill(dt); } } 原文链接：https://www.f2er.com/xml/298867.html