--	带前缀的查询方法
SELECT colxml 
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception[1]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception[2]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
FROM #XMLTab 

--	还可以简写为:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS p1)
SELECT colxml 
,colxml.exist('/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
,colxml.exist('/p1:Record/p1:Exception[1]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/p1:Record/p1:Exception[2]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
FROM #XMLTab 

--	元素判断：元素中的其他元素是否存在.为1则是该节点中不包含的.
SELECT colxml 
,colxml.exist('/Record/Exception[1][not(Other)]') --1：元素"Exception[1]"不包含元素"Other"
,colxml.exist('/Record/Exception[2][not(Other)]') --0：元素"Exception[2]"包含元素"Other"
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception[1][not(p1:Other)]')  --1：元素"p1:Exception[1]"不包含元素"p1:Other"
,colxml.exist('
	declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
	/p1:Record/p1:Exception[2][not(p1:Other)]')  --0：元素"p1:Exception[2]"包含元素"p1:Other"
FROM #XMLTab 

<??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KCjxwPjwvcD4KPHByZSBjbGFzcz0="brush:sql;">-- 使用"value"查询属性值和元素文本值 SELECT colxml.value('(/Record/Exception/Task/@address)[1]','varchar(100)') AS [address1] ,colxml.value('(/Record/Exception[2]/Task/@address)[1]','varchar(100)') AS [address2] ,colxml.value('data(/Record/Exception[1]/Error)[1]','int') AS [Error1] ,colxml.value('data(/Record/Exception[2]/Error)[1]','int') AS [Error2] ,colxml.value(' declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; data(/p1:Record/p1:Exception[2]/p1:Error)[1] ','int') AS [p1:Error2] FROM #XMLTab

--	使用"query"查询属性值
SELECT   
 CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)')) AS [id]  
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)[1]')) AS [type1] 
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)[2]')) AS [type2]  
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception[2]/@type)[1]')) AS [type2]
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)')) AS [type1 type2]
FROM #XMLTab

--	可以当做查询条件:
SELECT * FROM #XMLTab WHERE CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)'))='2015-05-05'

--	使用"nodes"中"value"查询属性值和元素文本值
SELECT node.c1.query('data(/Record/@dt)')
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)')) AS [id] 
,node.c1.value('(@dt)','VARCHAR(50)') AS [id] 
,node.c1.value('(@dt)[1]','VARCHAR(50)') AS [id] 
,node.c1.value('(Exception/@type)[1]','VARCHAR(50)') AS [type1]  
,node.c1.value('(Exception/Error)[1]','VARCHAR(50)') AS [Error1]  
FROM #XMLTab
CROSS APPLY colxml.nodes('/Record') as node(c1)

好了！~21图，测试到这。

XML类型的DML操作开始并不好记，看来只有多写语句才能记得。而这只是XML相关操作中很小的一部分。XML还涉及有类型化和非类型化的XML、XML类型导入导出、XML索引（主索引/辅索引/全文索引）、FOR XML的使用、XML架构集合等。太多！~有待学习！