SQL Server XML基础学习之<5>—XQuery(query)
周鸿运
T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。
XQuery 基于现有的 XPath 查询语言，并支持更好的迭代、更好的排序结果以及构造必需的 XML 的功能。
在前面我们已经学习了 XPath 的基本语法，本章将学习T-SQL 的 XQuery 语法
------------------------------------------------------------------------------------------------------------------
T-SQL XQuery包含如下函数
query(XPath条件)： 结果为 xml 类型; 返回由符合条件的节点组成的非类型化的 XML 实例
value(XPath条件,数据类型)：结果为指定的标量值类型; xpath条件结果必须唯一
exist(XPath条件)：结果为布尔值; 表示节点是否存在,如果执行查询的 XML 数据类型实例包含NULL则返回NULL
nodes(XPath条件): 返回由符合条件的节点组成的一行一列的结果表
*/
DECLARE @x XML
SET @x='
<root>
<rogue id="001">
<hobo id="1">
<name>彪</name>
<name>阿彪</name>
<type>流氓</type>
</hobo>
</rogue>
<rogue id="002">
<hobo id="2">
<name>光辉</name>
<name>二辉</name>
<type>流氓</type>
</hobo>
</rogue>
<rogue id="001">
<hobo id="3">
<name>小德</name>
<name>小D</name>
<type>臭流氓</type>
</hobo>
</rogue>
</root>'
--取root的所有子节点
--SELECT @x.query('root'),@x.query('/root'),@x.query('.')
--/*注释：
-- 这里实际上是取所有节点,root 必须是最高级节点名称，当换成任意子节点都是取不到值的
--*/
--取 hobo 的所有子节点,不管 hobo 在文档中的位置。
SELECT @x.query('//hobo')
----扩展：取rogue下 所有 name
SELECT @x.query('//rogue//name')
--取属性为id 的所有节点
SELECT @x.query('//hobo[@id]')
/*注释：
XQuery不支持直接顶级 attribute 节点，必须附带上对节点的查找
属性必须要加[]
*/
--选取属于 root 子元素的第一个 rogue 元素。
SELECT @x.query('/root/rogue[1]')
--选取属于 root 子元素的最后一个 rogue 元素。
SELECT @x.query('/root/rogue[last()]')
--选取属于 root 子元素的倒数第二个 rogue 元素。
SELECT @x.query('/root/rogue[last()-1]')
--选取最前面的两个属于 root 元素的子元素的 rogue 元素。
SELECT @x.query('/root/rogue[position()<3]')
--选取 root 元素的所有 hobo 元素，且其中的属性 id 的值须大于 1。
SELECT @x.query('/root//hobo[@id>1]')
----扩展： root 元素的所有 hobo 元素，且其中的属性 id 的值须大于 1 并且子节点 name 的值为 光辉 的。
SELECT @x.query('/root/rogue[./hobo[@id>1 and name="光辉"]]')
--选取 root 子元素的所有 rogue 元素，且 属性id 的值须大于 为001 子元素hobo 属性 id 的值为 1的
SELECT @x.query('/root/rogue[@id="001" and ./hobo[@id=1]]')
--if then else 表达式
SELECT @x.query('
if ( 1=2 ) then
/root/rogue[@id="001"]
else
/root/rogue[@id="002"]
')
--路径表达式步骤中的谓词
SELECT @x.query('/root/rogue[1]/hobo/name')--选择第一个 /root/rogue 节点下的所有 <Name> 元素。
SELECT @x.query('/root/rogue/hobo[1]/name')--选择 /root/rogue/hobo 节点下的所有 <Name> 元素。
SELECT @x.query('/root/rogue/hobo/name[1]')--选择 /root/rogue/hobo 节点下的所有第一个 <Name> 元素。
SELECT @x.query('(/root/rogue/hobo/name)[1]')--选择 /root/rogue/hobo 节点下的第一个 <Name> 元素。
--使用聚合函数
SELECT @x.query('count(/root/rogue/hobo/name)'),@x.query('count(/root/rogue/hobo/name[1])')
--FLWOR 迭代语法。FLWOR 是 for、let、where、order by 和 return 的缩写词。
--1
SELECT @x.query('
<result>
{ for $i in /root/rogue/hobo/name[1]
return string($i)
}
</result>')
--2
SELECT @x.query('
for $Loc in /root/rogue/hobo,
$FirstStep in $Loc/name[1]
return
string($FirstStep)
')
--3
SELECT @x.query('
for $i in /root/rogue/hobo
order by $i/@id descending
return string($i/name[1])
')
--4
SELECT @x.query('
for $i in /root/rogue/hobo
order by local-name($i)
return string($i/name[1])
')
/*
T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。
本章将分析XQuery的 value() 方法、 exist() 方法 和 nodes() 方法
*/
------------------------------value() 方法--------------------------------------
--value(XPath条件,数据类型)：结果为指定的标量值类型; XPath条件结果必须唯一
DECLARE @x XML
SET @x='
<root>
<rogue id="001">
<hobo id="1">
<name>彪</name>
<nickname>阿彪</nickname>
<type>流氓</type>
</hobo>
</rogue>
<rogue id="002">
<hobo id="2">
<name>光辉</name>
<nickname>二辉</nickname>
<type>流氓</type>
</hobo>
</rogue>
<rogue id="001">
<hobo id="3">
<name>小德</name>
<nickname>小D</nickname>
<type>臭流氓</type>
</hobo>
</rogue>
</root>'
--value() 方法从 XML 中检索 rogue 属性值。然后将该值分配给 int 变量。
SELECT @x.value('(/root/rogue/@id)[1]','int')
--解析 hobo 中属性 id 为2 的所有元素值
SELECT @x.value('(/root/rogue[2]/hobo/@id)[1]','int')
, @x.value('(/root/rogue[2]/hobo/name)[1]','varchar(10)')
, @x.value('(/root/rogue[2]/hobo/nickname)[1]','varchar(10)')
, @x.value('(/root/rogue[2]/hobo/type)[1]','varchar(10)')
------------------------------exist() 方法--------------------------------------
--exist() 方法- 用来判断 XQuery 表达式返回的结果是否为空
----判断 hobo 中属性 id 的值 是否为空
SELECT @x.exist('(/root/rogue/hobo/@id)[1]')
--判断指定节点值是否相等
DECLARE @xml XML ='<root><name>a</name></root>'
SELECT @xml.exist('(/root/name[text()[1]="a"])')
--用 exist() 方法比较日期时，请注意下列事项：
--代码 cast as xs:date? 用于将值转换为 xs:date 类型，以进行比较。
--@Somedate 属性的值是非类型化的。比较时，此值将隐式转换为比较右侧的类型（xs:date 类型）。
--可以使用 xs:date() 构造函数，而不用 cast as xs:date()。
DECLARE @a XML
SET @a='<root Somedate = "2012-01-01Z"/>'
SELECT @a.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2012-01-01")]')
--下面的示例与上一示例类似，不同之处在于它具有 <Somedate> 元素。
SET @a = '<Somedate>2002-01-01Z</Somedate>'
SELECT @a.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01") ]')
DECLARE @x1 XML
SELECT @x1 = '<Employee Number="1001" Name="Jacob"/>'
DECLARE @att VARCHAR(20)
SELECT @att = 'Number'
IF @x1.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
------------------------------nodes() 方法--------------------------------------
--语法
--nodes (XQuery) as Table(Column) 将一个 XQuery 表达式拆分成多行
--以便于拆分成关系数据
--将 rogue 节点拆分成多行
SELECT T.c.query('.') AS result
FROM @x.nodes('/root/rogue') T(c);
--扩展 rogue 拆分成数据行
SELECT T.c.value('(@id)[1]','varchar(10)') AS id
,T.c.value('(./hobo/name)[1]','varchar(10)') AS name
,T.c.value('(./hobo/nickname)[1]','varchar(10)') AS nickname
,T.c.value('(./hobo/type)[1]','varchar(10)') AS type
FROM @x.nodes('/root/rogue') T(c);
/**********************************************************
*
* value() 方法 nodes() 方法 exist() 方法的综合应用
*
**********************************************************/
--1 像下面的脚本，结点下还会用结点的，就要用到 text()
DECLARE @xml XML=N'
<a/>
<b>b<c>c</c></b>';
SELECT @xml.value('(/b)[1]', 'varchar(10)'), @xml.value('(/b/text())[1]', 'varchar(10)')
--2 对表中的 XML 数据进行解析, 节点下面有多个相同节点的 使用 CROSS APPLY 和 nodes() 方法解析
IF OBJECT_ID('tempdb..[#tb]') IS NOT NULL DROP TABLE [#tb]
CREATE TABLE [#tb]([id] INT,[name] XML)
INSERT [#tb]
SELECT 1,'<r><i>a</i><i>b</i></r>' UNION ALL
SELECT 2,'<r><i>b</i></r>' UNION ALL
SELECT 3,'<r><i>d</i></r>'
SELECT
T.c.query('.'),
T.c.value('.', 'sysname')
FROM [#tb] A
CROSS APPLY A.name.nodes('/r/i') T(c)
--3 利用xml 拆分字符串
DECLARE @s VARCHAR(100)='1,2,3,4,5,6'
SELECT t.c.value('.','int') AS col from
(SELECT CAST('<x>'+REPLACE(@s,',','</x><x>')+'</x>' AS XML ).query('.') AS name) AS a
CROSS APPLY a.name.nodes('/x') T(c)
--4 取任意属性的属性值，这里引入了 sql:variable
DECLARE @xml XML
DECLARE @Price DECIMAL(18 , 2),
@xmlPath VARCHAR(10)= 'Price2'
SET @xml='<row ProductID="1" Price1="111.00" Price2="222.00" Price3="333.00" Price4="444.00" Price5="555.00" Price6="666.00" Price7="777.00"/>'
SELECT col.value('(@Price2)[1]' , 'varchar(80)')
FROM @xml.nodes('/row') data(col)
SELECT @xml.value('(/row/@*[local-name()=sql:variable( "@xmlPath")])[1]' , 'DECIMAL(18,2)')
SELECT col.value('(@Price2)[1]' , 'varchar(80)')
FROM @xml.nodes('/row') data(col)
SELECT col.value('(@*[local-name()=sql:variable("@xmlPath")])[1]' , 'varchar(80)')
FROM @xml.nodes('/row') data(col)
--组合使用
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
DECLARE @pos INT
SELECT @pos = 2
SELECT
@x1.value('local-name(
(/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1]
)','VARCHAR(20)') AS AttName
--5 使用 WITH XMLNAMESPACES 声明前缀 以及 XQuery 函数 namespace-uri()
DECLARE @xml XML
SELECT @xml = '
<employee
xmlns="http://schemas.microsoft.com/sqlserver/emp"
xmlns:loc="http://schemas.microsoft.com/sqlserver/location"
name="Jacob" position="CTO"
loc:city="Ahmedabad" loc:state="Gujarat" loc:country="India"
/>'
--下面表达式将返回其命名空间 URI 为空的所有元素节点
--定义默认的命名空间
SELECT
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,
x.value('.', 'VARCHAR(20)') AS Value
FROM @xml.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/emp";
/employee/@*[namespace-uri()=""]') a(x)
--or 直接用通用符
SELECT
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,
x.value('.', 'VARCHAR(20)') AS Value
FROM @xml.nodes('/*/@*[namespace-uri()=""]') a(x)
-- 使用 WITH XMLNAMESPACES
;WITH XMLNAMESPACES(
DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'
)
SELECT
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,
x.value('.', 'VARCHAR(20)') AS Value
FROM @xml.nodes('/employee/@*[namespace-uri()=""]') a(x)
--返回所有带有前缀的节点
;WITH XMLNAMESPACES(
DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'
)
SELECT
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,
x.value('.', 'VARCHAR(20)') AS Value
FROM @xml.nodes('/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"]') a(x)
--返回所有带有前缀的节点个数统计
;WITH XMLNAMESPACES(
DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'
)
SELECT @xml.value('count(/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"])', 'int') AS [count]
--sql:column() 函数
--将普通数据列和 xml 数据列进行合并
DECLARE @t TABLE (id INT , data XML)
INSERT INTO @t (id , data)
SELECT 1 , '<root><name>二辉</name><type>流氓</type></root>'
UNION ALL
SELECT 2 , '<root><name>彪</name><type>流氓</type></root>'
SELECT id ,
data=data.query('<root>
<id>{sql:column("id")}</id>
{/root/name}
{/root/type}
</root>')
FROM @t
/*
id data
----------- ----------------------------------------------------
1 <root><id>1</id><name>二辉</name><type>流氓</type></root>
2 <root><id>2</id><name>彪</name><type>流氓</type></root>
*/
--根据一个xml 变量 与表中的值进行关联查询
DECLARE @tb TABLE (id INT)
INSERT INTO @tb(id)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
declare @XmlData xml
set @XmlData = '
<root>
<rogue><typeid>1</typeid><name>二辉</name><type>流氓</type></rogue>
<rogue><typeid>2</typeid><name>彪</name><type>流氓</type></rogue>
</root>'
SELECT t.id AS id
FROM @tb t
CROSS APPLY @XmlData.nodes('/root/rogue/typeid[. = sql:column("id")]') a(x)
--string-length() 函数 和 number() 函数
--提取长度为5的数字
DECLARE @t TABLE (CustomerID INT, CustomerAddress VARCHAR(50))
INSERT INTO @t(CustomerID, CustomerAddress)
SELECT 1, '12 20 97TH STREET NEW GARDENS, NY 11415 APT 8P' UNION ALL
SELECT 2, '20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR' UNION ALL
SELECT 3, '290 BERKELEY STREET APT24D NYC, NY 10038' UNION ALL
SELECT 4, '351-250 345 STREET PANAMA BEACH 11414 APT4F'
;WITH cte AS (
SELECT
CustomerID,
CAST('<i>' +REPLACE(CustomerAddress, ' ', '</i><i>') + '</i>' AS XML).query('.') AS CustomerAddress
FROM @t
)
SELECT
CustomerID,
x.i.value('.', 'VARCHAR(10)') AS ZipCode
FROM cte
CROSS APPLY CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') x(i)
/*
CustomerID ZipCode
----------- ----------
1 11415
2 11106
3 10038
4 11414
*/
--使用 contains() 函数进行模糊查询
SELECT a.x.value('name[1]' , 'varchar(10)')
FROM @x.nodes('/root/rogue/hobo') a (x)
CROSS APPLY a.x.nodes('type[contains(.,"臭流氓")]') b (y)
SELECT t.c.query('.')
FROM @x.nodes('/root/rogue/hobo[./type[contains(.,"臭流氓")]]') t (c)
/*------------------------------------------------------------------------------------------------------------------
本章讲述 modify() 方法 , modify() 方法是使用 XML DML 语句在 XML 数据中插入、更新或删除节点。
语法：modify (XML_DML) XML_DML 是 XML 数据操作语言 (DML) 中的字符串。
XML DML 将下列区分大小写的关键字添加到 XQuery 中：
insert --添加
delete --删除
replace value of --更新
------------------------------------------------------------------------------------------------------------------*/
DECLARE @x XML
SET @x='
<root>
<rogue id="001">
<hobo id="1">
<name>彪</name>
<type>流氓</type>
</hobo>
</rogue>
</root>'
------------------------------------------------------插入------------------------------------------------------------
--在 hobo 节点下插入 一个新节点
SET @x.modify('
insert <nickname>阿彪</nickname>
as first
into (/root/rogue/hobo)[1]
');
SELECT @x
--注释：如果某节点下面有多个节点的时候可以使用 as first 或 as last 来指定所需的新节点添加位置。
---在指定的 hobo 节点下，插入同一级节点
SET @x.modify('
insert <id>1</id>
before (/root/rogue/hobo)[1]
');
SELECT @x
--注释：是用 before 或者 after 关键字代替 into 在指定节点的 前面 或者 后面 插入同级节点
--after 关键字 和 before 关键字不能用于插入属性
--插入属性 一次插入多个属性值/使用变量/属性定位
DECLARE @a INT =5
SET @x.modify('
insert (
attribute a {sql:variable("@a")},
attribute b {".5"}
)
into (/root/rogue/hobo[@id=1])[1]
');
SELECT @x;
GO
----------------------------------------------------删除------------------------------------------------------------
DECLARE @x XML
SET @x='
<?Instructions for=TheWC.exe ?>
<root>
<rogue id="001">
<hobo id="1" weight="80" age="25">
<name>彪</name>
<type>流氓</type>
<nickname>阿彪</nickname>
<notes />
</hobo>
<page_hotspots />
</rogue>
<notes />
</root>'
-- 删除属性
SET @x.modify('
delete /root/rogue/hobo/@id
')
SELECT @x
-- 删除节点
SET @x.modify('
delete /root/rogue/hobo/name[1]
')
SELECT @x
-- 删除节点内容
SET @x.modify('
delete /root/rogue/hobo/type/text()
')
SELECT @x
-- 删除所有处理指令
SET @x.modify('
delete //processing-instruction()
')
SELECT @x
-- 删除所有的内容为空的节点
SET @x.modify('
delete //*[empty(./*)]
')
SELECT @x
----------------------------------------------------修改------------------------------------------------------------
DECLARE @x XML
SET @x='
<root>
<rogue id="001">
<hobo id="1" weight="80" age="25">
<name>彪</name>
<type>流氓</type>
<nickname>阿彪</nickname>
</hobo>
</rogue>
</root>'
-- 在修改语法当中 每次只能修改一个单个节点，不能批量修改或者一次修改多个值，这一点是比较郁闷的
-- 修改节点值
SET @x.modify('
replace value of (/root/rogue/hobo/name/text())[1]
with "光辉"
')
SELECT @x
-- 修改属性值
SET @x.modify('
replace value of (/root/rogue/hobo/@weight)[1]
with "70"
')
SELECT @x
-- 使用 if 表达式
SET @x.modify('
replace value of (/root/rogue/hobo/@age)[1]
with (
if (count(/root/rogue/hobo/*) > 2) then
"30"
else
"10"
)
')
SELECT @x
------------------------------------------------------一些示例------------------------------------------------------------
-- 1
DECLARE @x1 XML
SELECT @x1 = '
<rogue>
<hobo name="彪"/>
<hobo name="光辉"/>
<hobo name="小D"/>
<hobo name="野子"/>
</rogue>'
------------------------------------------------------------
-- 把 小D 移动到 彪 前面
------------------------------------------------------------
SET @x1.modify('
insert /rogue/hobo[@name="小D"]
before (/rogue/hobo[@name="彪"])[1]
')
SET @x1.modify ('
delete (/rogue/hobo[@name="小D"])[2]
')
SELECT @x1
---------- --------------------------------------------------
-- 把 光辉 移动到 野子 后面
------------------------------------------------------------
SET @x1.modify('
insert /rogue/hobo[@name="光辉"]
after (/rogue/hobo[@name="野子"])[1]
')
SET @x1.modify ('
delete (/rogue/hobo[@name="光辉"])[1]
')
SELECT @x1
------------------------------------------------------------
-- 把 野子 向前移动一级
------------------------------------------------------------
SET @x1.modify('
insert /rogue/hobo[@name="野子"]
before (/rogue/hobo[. << (/rogue/hobo[@name="野子"])[1]])[last()]
')
SET @x1.modify ('
delete /rogue/hobo[@name="野子"]
[. is (/rogue/hobo[@name="野子"])[last()]]
')
SELECT @x1
------------------------------------------------------------
-- 把 彪 向后 移一级
------------------------------------------------------------
set @x1.modify('
insert /rogue/hobo[@name="彪"]
before (/rogue/hobo[. >> (/rogue/hobo[@name="彪"])[1]])[2]
')
SELECT @x1
SET @x1.modify ('
delete (/rogue/hobo[@name="彪"])[1]
')
SELECT @x1
------------------------------------------------------------
-- 使用变量修改属性名称
------------------------------------------------------------
DECLARE @x2 XML
SELECT @x2 = '
<Employees>
<Employee FirstName="Jacob" MiddleName="V" LastName="Sebastian"/>
</Employees>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'MiddleName'
SELECT @val = 'J'
SET @x2.modify('
replace value of (
/Employees/Employee/@*[local-name()=sql:variable("@var")]
)[1]
with sql:variable("@val")
')
select @x2
类似资料：