XML 查询（摘录）

数据库中有一些XML列，可以使用查询方式得到XML中的部分数据。

SQL Server提供了5种方式操作XML中的数据query(),value(),exist(),modify(),nodes()，这些方法在一些场景下还是比较有用的。

1. 对xml 类型的变量使用query() 方法

 ---该查询检索 <ProductDescription> 元素的 <Features> 子元素：

declare @myDoc xml
set @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
SELECT @myDoc.query('/Root/ProductDescription/Features')

---对 XML 类型列使用 query() 方法

SELECT CatalogDescription.query('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" />
') as Result
FROM Production.ProductModel
where CatalogDescription.exist('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1

2. value (XQuery, SQLType)

---value() 方法从 XML 中检索 ProductID 属性值。然后将该值分配给 int 变量

DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
SELECT @ProdID

---以下查询根据 AdventureWorks 数据库中的 xml 类型列 (CatalogDescription) 指定。查询从列中存储的每个 XML 实例中检索 ProductModelID 属性值

SELECT CatalogDescription.value('           
    declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";           
       (/PD:ProductDescription/@ProductModelID)[1]', 'int') AS Result           
FROM Production.ProductModel           
WHERE CatalogDescription IS NOT NULL           
ORDER BY Result desc           
3. exist (XQuery)
SELECT ProductModelID, CatalogDescription.query('
declare namespace pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
    <Product 
        ProductModelID= "{ sql:column("ProductModelID") }" 
        />
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('
    declare namespace  pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     /pd:ProductDescription[not(pd:Specifications)]'
    ) = 1
4.nodes (XQuery) as Table(Column)
 
SELECT C.query('.') as result
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7