/*-SQL中XML区分大小写--*//*1.搜索单个值*/declare @myDoc xmldeclare @ProdID nvarchar(20)set @myDoc = '<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate= "1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <ShowName>vinet1</ShowName> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate= "1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> <ShowName>vinet2</ShowName> </Order></Customer></ROOT>'--搜索属性set @ProdID = @myDoc.value('(/ROOT/Customer/@CustomerID)[1]', 'nvarchar(20)' )select @ProdID--搜索内容set @ProdID = @myDoc.value('(/ROOT/Customer/Order/ShowName)[1]', 'nvarchar(20)' )select @ProdID---或DECLARE @docHandle intDECLARE @XmlDocument nvarchar(1000)SET @XmlDocument = N'<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order EmployeeID="5" > <OrderID>10248</OrderID> <CustomerID>VINET</CustomerID> <OrderDate>1996-07-04T00:00:00</OrderDate> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order EmployeeID="3" > <OrderID>10283</OrderID> <CustomerID>LILAS</CustomerID> <OrderDate>1996-08-16T00:00:00</OrderDate> <OrderDetail ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail[1]')WITH (CustomerID varchar(10) '../CustomerID', OrderDate datetime '../OrderDate', ProdID int '@ProductID', Qty int '@Quantity')EXEC sp_xml_removedocument @docHandle---exist--属性SELECT xColFROM TWHERE xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1--值SELECT *FROM tbUserWHERE LabelXml.exist ('/Beisen/label[.="绩优人员1"]') = 1--值likeSELECT *FROM tbUserWHERE LabelXml.exist ('/Beisen/label[contains(.,"绩优人员1")]') = 1--值likeSELECT *FROM tbUserWHERE LabelXml.exist ('/Beisen/label/text()[contains(.,"绩优人员1")]') = 1 OR LabelXml.exist ('/Beisen/label/text()[contains(.,"绩优人员")]') = 1
更新：
ALTER PROCEDURE [dbo].[TerminalTest_AddInterrupt] -- Add the parameters for the stored procedure here @resultID int, @testID uniqueidentifierASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Declare @interrupt int set @interrupt=(select Result.value('(/TestResult/test[id= sql:variable("@testID")]/interrupt)[1]', 'int' ) from [dbo].[AssessmentUserTestResult] where ID=@resultID)+1
update [dbo].[AssessmentUserTestResult] SET Result.modify(' replace value of (/TestResult/test[id= sql:variable("@testID")]/interrupt[1]/text())[1] with sql:variable("@interrupt") ') where ID=@resultID END
sDECLARE @isbn varchar(20)SET @isbn = '绩优人员1'SELECT *FROM tbUserWHERE LabelXml.exist ('/Beisen/label/text()[. = sql:variable("@isbn")]') = 1
Sqlserver中有一字段存如下XML：
－－－－－－－－－－－－－－－－－－－－－－－－
难点：
<Root>
<UserFlag>1002</UserFlag>
<UserFlag>1003</UserFlag>
<UserFlag>1005</UserFlag>
</Root>
需要搜索包含UserFlag等于1002或1003。。。。N (UserFlag数可能有2个也可能有3，5个)
谁有好的解决方法，提供一下。。谢谢
解决方法一：
select * from tbUser where Exists( select * from (SELECT tbUser.userid,T2.Loc.value( '.[1] ', 'int') as flagFROM tbUserCROSS APPLY LabelXml.nodes('/Root/UserFlag') as T2(Loc) ) as b where b.userid=tbUser.userid and b.flag in ('102','101'))
