1 使用query, value和exist方法带xml变量
SELECT nCol.value(''''@ProductID'''',''''int'''') Product,
  nCol.valus(''''@Quantity'''',''''int'''') Qty
FROM @xmlOrder.nodes(''''/Order/ListItem'''')
AS nTable(nCol)
----------------------------------------------------------
declare @myDoc xml
set @myDoc = ''''
<bookstore>
  <book category="COOKING">
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER">
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE">
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>
''''
select @myDoc.query(''''/bookstore/book/title'''')
-----------------------------
select @myDoc.query(''''/bookstore/book[price>30]'''')
select @myDoc.query(''''for $x in /bookstore/book
where $x/price>30
return $x/title'''')

select @myDoc.query(''''for $x in /bookstore/book/title
order by $x
return $x'''')

select @myDoc.query(''''for $x in /bookstore/book/title
return <li>{data($x)}</li>'''')

select @myDoc.query(''''for $x in /bookstore/book/title
order by $x
return <li>{data($x)}</li>'''')
----------------------------------------------------------
declare @myDoc xml
set @myDoc = ''''
<bookstore>
  <book category="COOKING" id="1" >
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>
''''
value查询

select @myDoc.value(''''(/bookstore/book/@id)[1]'''',''''int'''')

exist查询

select @myDoc.exist(''''/bookstore/book/title="VS.NET2003"'''')
select @myDoc.exist(''''/bookstore/book[@id=1]'''')
---------------------------------------------------------

结果集中绑定表中列

select orderid,''''L01'''' as ProductID,Customer,
Details.query(''''
<OrderDetails>
<Customer>{sql:column("Customer")}</Customer>
    {
  for $x in //row
  return $x
}
</OrderDetails>
'''')
from orders

2 使用APPLY运算符
SELECT nCol.value(''''../@OrderID[1]'''',''''int'''') ID,
  nCol.valus(''''@ProductID[1]'''',''''int'''') Prod
FROM Sales.Orders
CROSS APPLY OrderDoc.nodes(''''/Order/ListItem'''')
AS nTable(nCol)

上一页  [1] [2] [3]