Sql 操作xml

/*-SQL中XML区分大小写--*/

/*1.搜索单个值*/
declare @myDoc xml
declare @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 int
DECLARE @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 xCol
FROM   T
WHERE  xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1

--值
SELECT *
FROM   tbUser
WHERE  LabelXml.exist ('/Beisen/label[.="绩优人员1"]') = 1

--值like
SELECT *
FROM   tbUser
WHERE  LabelXml.exist ('/Beisen/label[contains(.,"绩优人员1")]') = 1

--值like
SELECT *
FROM   tbUser
WHERE  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 uniqueidentifier
AS
BEGIN
 -- 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

 

s
DECLARE @isbn varchar(20)
SET     @isbn = '绩优人员1'
SELECT  *
FROM    tbUser
WHERE   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 flag
FROM tbUser
CROSS APPLY LabelXml.nodes('/Root/UserFlag') as T2(Loc)
) as b where b.userid=tbUser.userid and b.flag in ('102','101'))