sqlserver 查询根据索引字段查询记录时,如果要查询的记录非常多,需要将要查询的值转为xml,并导入临时表中进行查询。
写法:1
DECLARE @XMLDocument XML SET @XMLDocument='<Root> <Row><A>898989898</A><B>123213</B></Row> <Row><A>898989897</A><B>123212</B></Row> </Root>' SELECT T.c.value('(B[1])', 'int') AS Id, T.c.value('(A[1])', 'varchar(20)') AS Id_No INTO #temp FROM @XMLDocument.nodes('/Root/Row') AS T ( c ); SELECT * FROM #temp; DROP TABLE #temp;
写法:2
DECLARE @XMLDocument XML SET @XMLDocument='<Root> <Row id="173528" /> <Row id="173527" /> </Root>' SELECT T.c.value('@id', 'int') AS id INTO #temp FROM @XMLDocument.nodes('/Root/Row') AS T ( c ); SELECT * FROM #temp; DROP TABLE #temp;