SQL Server操作XML(五)XML Query-XQuery

(1)索引

XML字段最大支持2G，如果不建立索引，遍历数据查询，性能会很差。

• Primary Index             必须有主键列，且为聚集索引。该索引中存放XML数据中Tag、Value和Path等信息
  
• 辅助Path Index            用于基于Path查询，即XPath查询
  
• 辅助Property Index      用于基于节点的查询
  
• 辅助Value Index          用于XML值的查询

XQuery即查询XML语言，包括基于XPath、Element和Attribute

语法包括FLOWR

For                遍历满足条件节点的内容

Let           

Order By

Where

Return

(2)索引查询

原始数据

 

CREATE TABLE Orders
(OrderID int IDENTITY(1,1),
 Customer VARCHAR(20),
 SaleDate Date,
 OrderDetail xml)

insert orders values('Tom','2009-09-13','
<OrderDetails>
  <row>
     <Product_ID>2</Product_ID>
     <Quantity>200</Quantity>
  </row>
  <row>
     <Product_ID>3</Product_ID>
     <Quantity>300</Quantity>
  </row>
  <row>
     <Product_ID>1</Product_ID>
     <Quantity>400</Quantity>
  </row>
</OrderDetails>
')

 

• Primary Index

 

 

create primary xml index xidx_details on orders(OrderDetail)

显示错误：表 'orders' 需要具有一个包含的列数小于 16 的聚集主键，才能为其创建主 XML 索引。

 

 原因是缺少聚集的主键列

 

alter table orders
add constraint pk_orders_orderid
primary key clustered(orderid)

 

• Path Index

 

--在primary index基础上,创建path index
create xml index xidx_details_path on orders(OrderDetail) using xml index xidx_details for path

 

 基于Path Index查询，按照相对路径//OrderDetails/row查询

 

 

select OrderID,OrderDetail.query('//OrderDetails/row') from orders

显示结果

<row>
  <Product_ID>2</Product_ID>
  <Quantity>200</Quantity>
</row>
<row>
  <Product_ID>3</Product_ID>
  <Quantity>300</Quantity>
</row>
<row>
  <Product_ID>1</Product_ID>
  <Quantity>400</Quantity>
</row>

 

 

 

•  Property Index

 

--在primary index基础上,创建property index
create xml index xidx_details_property on orders(OrderDetail) using xml index xidx_details for property

基于Property Index查询，按照相对路径//OrderDetails/row查找相同深度的第二个元素

select OrderID,OrderDetail.query('//OrderDetails/row[Product_ID][2]') from orders

显示结果：

<row>
  <Product_ID>L02</Product_ID>
  <Quantity>300</Quantity>
</row>

 

•  Value Index

--在primary index基础上,创建value index
create xml index xidx_details_value on orders(OrderDetail) using xml index xidx_details for value

--基于value index查询,按照相对路径//OrderDetails/row查找Product_ID="L01"的元素
select OrderID,OrderDetail.query('//OrderDetails/row[Product_ID="L01"]') from orders

 

显示结果：

 

<row>
  <Product_ID>L01</Product_ID>
  <Quantity>200</Quantity>
</row>

 

 

 

 详细介绍如下：

 

declare @mydoc xml

set @mydoc='
<ROOT>
<AAA>
  <BBB ID="1">二层第一个B</BBB>
  <BBB ID="2">二层第二个B</BBB>
  <CCC>
    <DDD>  
    <BBB ID="3">四层第一个B</BBB>
    </DDD>
    <BBB ID="4">三层第一个B</BBB>
    <BBB ID="5">三层第二个B</BBB>
   </CCC>
   <DDD>100</DDD>
   <DDD>200</DDD>
</AAA>

<AAA>
   <DDD>300</DDD>
   <DDD>400</DDD>
</AAA>
</ROOT>

'

--绝对路径，按照ROOT/AAA/BBB路径查找
select @mydoc.query('/ROOT/AAA/BBB')

 显示结果

<BBB ID="1">二层第一个B</BBB>
<BBB ID="2">二层第二个B</BBB>

 

--相对路径，按照所有BBB路径查找
select @mydoc.query('//BBB')

 显示结果

 

<BBB ID="1">二层第一个B</BBB>
<BBB ID="2">二层第二个B</BBB>
<BBB ID="3">四层第一个B</BBB>
<BBB ID="4">三层第一个B</BBB>
<BBB ID="5">三层第二个B</BBB>

 

 

--相同深度的第一个BBB
select @mydoc.query('//BBB[1]') 

 

 显示结果 

 

<BBB ID="1">二层第一个B</BBB>
<BBB ID="3">四层第一个B</BBB>
<BBB ID="4">三层第一个B</BBB>

 

--绝对路径，按照ROOT/AAA/BBB路径查找的第一个BBB
select @mydoc.query('/ROOT/AAA/BBB[1]')

显示结果：

 

<BBB ID="1">二层第一个B</BBB>

 

 

--绝对路径，按照ROOT/AAA/BBB路径查找的最后一个BBB
select @mydoc.query('/ROOT/AAA/BBB[last()]') 

 

显示结果：

<BBB ID="2">二层第二个B</BBB>

 

--相对路径，按照所有BBB路径查找ID=4的BBB(针对Attribute)
select @mydoc.query('//BBB[@ID="4"]')

显示结果：

<BBB ID="4">三层第一个B</BBB>

 

--绝对路径，按照所有BBB路径查找DDD=300的整棵树信息(针对Element)
select @mydoc.query('/ROOT/AAA[DDD=300]')

 显示结果：

 

 

<AAA>
  <DDD>300</DDD>
  <DDD>400</DDD>
</AAA>

其中，/表示绝对路径，/AAA/BBB可以找到，但是路径不完整，则找不到例如/BBB

       //表示相对路径，会自动补全所有上级路径，例如//BBB可以找到所有****/BBB路径