XML与SQL SERVER2005 (五)
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-09-02
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-- 本文探讨 SQL Server 2005 中内置的 XML 支持.
-- 第五篇旨对路径表达式中的轴步骤谓词详细说明。
-- 本文来自happyflystone -CSDN博客,
-- 转载请标明出处:http://blog.csdn.net/happyflystone
------------------------------------------------------------------------
3、 谓词
谓词表达式用方括号括起来,来筛选特定的节点序列。
来来,先看个例子吧
declare @xml xml
set @xml = '<address>
<exam>
<name>
<firstname>fly</firstname>
<lastname>stone1</lastname>
</name>
<street> 1401 Main Street </street>
<city state="NC">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
<exam>
<name>
<firstname>fly</firstname>
<lastname>stone2</lastname>
</name>
<street> 1402 Main Street </street>
<city state="NM">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
</address>
'
--1
select @xml.query('//address/exam/name/firstname[1]')
/*
-------------------------------------------------------
<firstname>fly</firstname><firstname>fly</firstname>
(1 行受影响)
*/
--2
select @xml.query('//address/exam[1]/name/firstname')
/*
---------------------------------------------------
<firstname>fly</firstname>
(1 行受影响)
*/
--3
select @xml.query('(//address/exam)[1]/name/lastname')
/*
-----------------------------------------------------
<lastname>stone1</lastname>
(1 行受影响)
*/
--4
select @xml.query('(//address/exam)[2]/name/lastname')
/*
-----------------------------------------------------
<lastname>stone2</lastname>
(1 行受影响)
*/上面表达式中的[1]、[2]就是谓词,大家注意括号对路径表达式的影响哦,在本例第三种写法大家自己比较吧,另外比较第三种与第四种的不同及结果的不同哈。
谓词自身的括号内可多次使用和添加量词,下面这个例子:
select @xml.query('//address/exam/name/lastname[1]')
select @xml.query('//address/exam[contains(street[1],"1402")]/name/lastname[1]')
select @xml.query('//address/exam[contains(street[1],"1402") and xs:integer(postal-code[1])>200000]/name/lastname[1]')
/*
--------------------------------------------------------
<lastname>stone1</lastname><lastname>stone2</lastname>
(1 行受影响)
---------------------------------------------------------
<lastname>stone2</lastname>
(1 行受影响)
---------------------------------------------------------
<lastname>stone2</lastname>
(1 行受影响)
*/
注:当谓词表达式的值是一个空序列时的情况:
select @xml.query('//address/exam/name[attribute::idq]')
select @xml.query('//address/exam/name[attribute::id]/firstname[1]')
嘿嘿,大体上说完轴步骤了,下回的议题容我想好再定!!