XQuery Charles

XQuery 是MSSQL SERVER对XML文档的相关操作。

一些书本上说 SQL SERVER 2005 所支持的 XQuery语法包括 FLWOR 即，for,let,where,order by 和 return.

但实际操作中发现SQL SERVER 2005 并不支持 “let”.

以下代码在SQL SERVER 2008 上能够正常执行，实现对XML简单的数据统计功能。

DECLARE @x XML
SELECT @x='
<invoices>
  <invoice>
    <Customer>Charles1</Customer>
    <items>
       <item ProductId="4" Price="75.00" /> 
       <item ProductId="5" Price="12.00" /> 
    </items>
  </invoice>
    <invoice>
    <Customer>Charles2</Customer>
    <items>
       <item ProductId="7" Price="552.00" /> 
       <item ProductId="9" Price="514.00" /> 
       <item ProductId="13" Price="758.00" /> 
       <item ProductId="19" Price="126.00" /> 
    </items>
  </invoice>
    <invoice>
    <Customer>Charles3</Customer>
    <items>
       <item ProductId="8" Price="550.00" /> 
       <item ProductId="16" Price="543.00" /> 
       <item ProductId="10" Price="755.00" /> 
    </items>
  </invoice>
</invoices>
'
SELECT @x.query(
'
   <Orders>
     {
      for $invoice in /invoices/invoice
      let $count :=count($invoice/items/item)
      let $count2:=count($invoice/items/item)
      order by $count
      return
      <Order>
        {$invoice/Customer}
        {<Itemcount>{$count}, {$count2}</Itemcount>}
        {<Itemcount>{$count,$count2}</Itemcount>}
      </Order>
     }
   </Orders>
'
) XQuery_Result

执行结果如下：

<Orders>
  <Order>
    <Customer>Charles1</Customer>
    <Itemcount>2, 2</Itemcount>
    <Itemcount>2 2</Itemcount>
  </Order>
  <Order>
    <Customer>Charles3</Customer>
    <Itemcount>3, 3</Itemcount>
    <Itemcount>3 3</Itemcount>
  </Order>
  <Order>
    <Customer>Charles2</Customer>
    <Itemcount>4, 4</Itemcount>
    <Itemcount>4 4</Itemcount>
  </Order>
</Orders>