34,596
社区成员
发帖
与我相关
我的任务
分享
DECLARE @x XML
SELECT @x='<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<anyType xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid"> 7a892bf3-059c-4675-a11f-bea56a813d8d </anyType>
<anyType xmlns:q2="http://microsoft.com/wsdl/types/" xsi:type="q2:guid"> 25855649-c6cb-47c3-a978-c49873f3b7e6 </anyType>
</ArrayOfAnyType>'
DECLARE @t TABLE
(id VARCHAR(100),v VARCHAR(100))
INSERT @t SELECT 'asdfasdfasdf','aaa'
UNION ALL SELECT 'ddadfasdfasdf','bbb'
UNION ALL SELECT '25855649-c6cb-47c3-a978-c49873f3b7e6','ccc'
SELECT a.* FROM @t a
INNER JOIN
@x.nodes('//anyType') AS t(tid)
ON RTRIM(LTRIM(CAST(tid.query('./text()') AS VARCHAR(100))))=id
DECLARE @x XML
SELECT @x='<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<anyType xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid"> 7a892bf3-059c-4675-a11f-bea56a813d8d </anyType>
<anyType xmlns:q2="http://microsoft.com/wsdl/types/" xsi:type="q2:guid"> 25855649-c6cb-47c3-a978-c49873f3b7e6 </anyType>
</ArrayOfAnyType>'
DECLARE @t TABLE
(id VARCHAR(100),v VARCHAR(100))
INSERT @t SELECT 'asdfasdfasdf','aaa'
UNION ALL SELECT 'ddadfasdfasdf','bbb'
UNION ALL SELECT '25855649-c6cb-47c3-a978-c49873f3b7e6','ccc'
SELECT * FROM @t WHERE CHARINDEX(CHAR(32) + id + CHAR(32),CHAR(32) + CAST(@x.query('//anyType/text()') AS VARCHAR(8000)) + CHAR(32))>0
/*
id v
25855649-c6cb-47c3-a978-c49873f3b7e6 ccc
*/