获取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


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>

转载于:https://www.cnblogs.com/lilycnblogs/archive/2010/03/31/1701625.html