This website requires JavaScript.

Stairway to XML: Level 4 XML 数据查询[译]

在SQL Server 中XML格式的数据支持5种方法—query(),value(),exist(),nodes(),和modify() 本文中我们主要讲解两个查询方法 query()和value()

每个XML方法至少需要一个XQuery表达式参数.XQuery是一个用来访问XML数据的强大脚本语言.该语言包含函数,操作符,变量,值以及其他必要的元素来创建复杂表达式.SQL Server 支持XQuery语言的子集,用他们创建的表达式可你可以用来获取或修改XML实例数据.

因为XQuery是个复杂语言,本文中只涉及常用内容.更多底层细节以及在SQL Server中的实现,请参考MSDN XQuery language reference

设置测试环境和案例数据

以下代码创建了一个名为ClientInfoCollection的XML schema以及ClientInfo表,并插入了一行xml数据. 其中 Info_typed 列是类型化的XML Info_untyped 列是未类型化的XML列. 类型化的列使用了ClientInfoCollection XML schema, schema的namespace为 urn``:ClientInfoNamespace 关于XML schema 的信息可以参考Level 2

USE master; GO

IF DB_ID('ClientDB') IS NOT NULL DROP DATABASE ClientDB; GO

CREATE DATABASE ClientDB; GO

USE ClientDB; GO

IF OBJECT_ID('ClientInfoCollection') IS NOT NULL DROP XML SCHEMA COLLECTION ClientInfoCollection; GO

CREATE XML SCHEMA COLLECTION ClientInfoCollection AS '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:ClientInfoNamespace" targetNamespace="urn:ClientInfoNamespace" elementFormDefault="qualified"> <xsd:element name="People"> <xsd:complexType> <xsd:sequence> <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" /> <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" /> <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" /> </xsd:sequence> <xsd:attribute name="id" type="xsd:integer" use="required"/> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; GO

IF OBJECT_ID('ClientInfo') IS NOT NULL DROP TABLE ClientInfo; GO

CREATE TABLE ClientInfo ( ClientID INT PRIMARY KEY IDENTITY, Info_untyped XML, Info_typed XML(ClientInfoCollection) );

INSERT INTO ClientInfo (Info_untyped, Info_typed) VALUES ( '<?xml version="1.0" encoding="UTF-8"?> <People> <Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person> <Person id="5678"> <FirstName>Jane</FirstName> <LastName>Doe</LastName> </Person> </People>', '<?xml version="1.0" encoding="UTF-8"?> <People xmlns="urn:ClientInfoNamespace"> <Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person> <Person id="5678"> <FirstName>Jane</FirstName> <LastName>Doe</LastName> </Person> </People>' );

XML query() 方法

这个方法一般用来返回非类型化XML,使用这个方法,语法如下:

_db_``__object_``.query('``_xquery_exp_``')

1.使用query()方法返回非类型化<People>元素

让我们看个例子,我使用query()方法查询Info_untyped 列,首先指定列明,然后添加表达式<code>/People.即返回&lt;People&gt;元素

SELECT Info_untyped.query('/People') AS People_untyped FROM ClientInfo;
返回结果如下,返回了<People>元素和内容(子元素,属性和值).
<People> <Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person> <Person id="5678"> <FirstName>Jane</FirstName> <LastName>Doe</LastName> </Person> </People>

2. 使用query()方法返回类型化XML列

要返回类型化XML需要在XQuery表达式里面加上XSD schema中指定的namespace ,语句如下:

SELECT Info_typed.query( 'declare namespace ns="urn:ClientInfoNamespace"; /ns:People') AS People_typed FROM ClientInfo;
我们可以注意到查询表达式被拆成两行,中间隔了一个分号(;). 第一行用来声明命名空间,使用 declare namespace 关键字,然后跟着一个namespace的别名(例子中的ns) 别名后面跟着的是namespace (urn``:ClientInfoNamespace) .

第二行则跟我们前面用来查询非类型化的表达式差不多,只是在前面加了一个别名和冒号(ns:)执行结果如下:

<People xmlns="urn:ClientInfoNamespace"> <Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person> <Person id="5678"> <FirstName>Jane</FirstName> <LastName>Doe</LastName> </Person> </People>

3. 获取<Person>元素

如果要显示People下面Person元素可以使用以下查询语句

SELECT Info_untyped.query( '/People/Person') AS People_untyped, Info_typed.query( 'declare namespace ns="urn:ClientInfoNamespace"; /ns:People/ns:Person') AS People_typed FROM ClientInfo;
结果如下:

非类型化结果

<Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person> <Person id="5678"> <FirstName>Jane</FirstName> <LastName>Doe</LastName> </Person>
类型化结果
<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234"> <ns:FirstName>John</ns:FirstName> <ns:LastName>Doe</ns:LastName> </ns:Person> <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678"> <ns:FirstName>Jane</ns:FirstName> <ns:LastName>Doe</ns:LastName> </ns:Person>

4. 返回指定的<Person>元素

可以利用Person的属性值来指定返回的元素,代码如下:

SELECT  Info_untyped.query('/People/Person[@id=1234]') AS People_untyped ,       Info_typed.query('declare namespace ns="urn:ClientInfoNamespace"; /ns:People/ns:Person[@id=5678]') AS People_typed FROM    ClientInfo;
结果如下:

非类型化

<Person id="1234"> <FirstName>John</FirstName> <LastName>Doe</LastName> </Person>
类型化
<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678"> <ns:FirstName>Jane</ns:FirstName> <ns:LastName>Doe</ns:LastName> </ns:Person>

5. 返回指定元素<Person>下面的<FirstName>元素

SELECT  Info_untyped.query('/People/Person[@id=1234]/FirstName') AS People_untyped
,       Info_typed.query('declare namespace ns="urn:ClientInfoNamespace";
 /ns:People/ns:Person[@id=5678]/ns:FirstName') AS People_typed
FROM    ClientInfo;
结果如下:

非类型化

<FirstName>John</FirstName>
类型化
<ns:FirstName xmlns:ns="urn:ClientInfoNamespace">Jane</ns:FirstName>

6. 指定实例号获取<Person>元素

如果不确定具体属性值,则可以用[1],[2]这种指定位置获取数据

SELECT  Info_untyped.query('/People/Person[1]/FirstName') AS People_untyped ,       Info_typed.query('declare namespace ns="urn:ClientInfoNamespace"; /ns:People/ns:Person[2]/ns:FirstName') AS People_typed FROM    ClientInfo;

XML value() 方法

顾名思义,这个方法用来直接获取元素或者属性值.语法格式如下:

_db_``__object_``.value('``_xquery_exp_``', '``_sql_type_``')

1. 查询<FirstName>的值

SELECT  Info_untyped.value('(/People/Person[1]/FirstName)[1]', 'varchar(20)') AS Name_untyped
,       Info_typed.value('declare namespace ns="urn:ClientInfoNamespace";
 (/ns:People/ns:Person[2]/ns:FirstName)[1]', 'varchar(20)') AS Name_typed
FROM    ClientInfo;
结果如下:

Name_untyped         Name_typed


John                 Jane

2. 获取id属性值

SELECT  Info_untyped.value('(/People/Person/@id)[1]', 'int') AS Name_untyped
,       Info_typed.value('declare namespace ns="urn:ClientInfoNamespace";
 (/ns:People/ns:Person/@id)[2]', 'int') AS Name_typed
FROM    ClientInfo;
结果如下:

Name_untyped         Name_typed


1234                 5678

3.计算<Person>元素的个数

SELECT  Info_untyped.value('count(/People/Person)', 'int') AS Number_untyped
,       Info_typed.value('declare namespace ns="urn:ClientInfoNamespace";
 count(/ns:People/ns:Person)', 'int') AS Number_typed
FROM    ClientInfo;
结果如下

Number_untyped Number_typed


2              2

4.使用concat()函数连接两个值

SELECT  Info_untyped.value('concat((/People/Person/FirstName)[2], " ",
 (/People/Person/LastName)[2])', 'varchar(25)') AS FullName
FROM    ClientInfo;
结果如下:

FullName

Jane Doe

原文地址

http://www.sqlservercentral.com/articles/Stairway+Series/+Querying+XML+Data/92784/

0条评论
avatar