sql server XML中value(),exists(),nodes()方法

转载

ian_slq 2021-11-16 14:12:11 博主文章分类：SQL

文章标签 xml sql microsoft 数据结点 文章分类 代码人生

T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。

本章将分析XQuery的 value() 方法、 exist() 方法和 nodes() 方法

------------------------------value() 方法--------------------------------------

–value(XPath条件,数据类型)：结果为指定的标量值类型; XPath条件结果必须唯一

DECLARE @x XML

SET @x=’

彪

阿彪

流氓

光辉

二辉

流氓

小德

小D

臭流氓

’

–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 =‘a’

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=’’

SELECT @a.exist(’/root[(@Somedate cast as xs:date?) eq xs:date(“2012-01-01”)]’)

–下面的示例与上一示例类似，不同之处在于它具有元素。

SET @a = ‘2002-01-01Z’

SELECT @a.exist(’/Somedate[(text()[1] cast as xs:date ?) = xs:date(“2002-01-01”) ]’)

DECLARE @x1 XML

SELECT @x1 = ‘’

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©;

–扩展 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©;

/**********************************************************

value() 方法 nodes() 方法 exist() 方法的综合应用

**********************************************************/

–1 像下面的脚本，结点下还会用结点的，就要用到 text()

DECLARE @xml XML=N’

bc’;

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,‘ab’ UNION ALL

SELECT 2,‘b’ UNION ALL

SELECT 3,‘d’

SELECT

T.c.query(’.’),

T.c.value(’.’, ‘sysname’)

FROM [#tb] A

CROSS APPLY A.name.nodes(’/r/i’) T©

–3 利用xml 拆分字符串

DECLARE @s VARCHAR(100)=‘1,2,3,4,5,6’

SELECT t.c.value(’.’,‘int’) AS col from

(SELECT CAST(’’+REPLACE(@s,’,’,’’)+’’ AS XML ).query(’.’) AS name) AS a

CROSS APPLY a.name.nodes(’/x’) T©

–4 取任意属性的属性值，这里引入了 sql:variable

DECLARE @xml XML

DECLARE @Price DECIMAL(18 , 2),

@xmlPath VARCHAR(10)= ‘Price2’

SET @xml=’’

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 = ’

’

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 = ’

’

–下面表达式将返回其命名空间 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 , ‘二辉流氓’

UNION ALL

SELECT 2 , ‘彪流氓’

SELECT id ,

data=data.query(’

{sql:column(“id”)}

{/root/name}

{/root/type}

FROM @t

id data

1二辉流氓

2彪流氓

–根据一个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 = ’

1二辉流氓

2彪流氓

’

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(’’ +REPLACE(CustomerAddress, ’ ‘, ‘’) + ‘’ 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

–使用 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 ©

按 Ctrl+C 复制代码

/------------------------------------------------------------------------------+
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : |
#|{>/------------------------------------------------------------------------<}|
#|: | Author : 小爱 |
#|: | Description: XQuery的 value() 方法、 exist() 方法和 nodes() 方法 |
#|: | SQL Version: 适用于 SQL 2005, SQL 2008 , SQL 2012 |
#|: | Copyright : 转载请注明出处。更多请访问：http://blog.csdn.net/beirut |
#|: | Create Date: 2012-11-22 |
#|: | About Me : 一个菜鸟dba |
#|{>------------------------------------------------------------------------/<}|
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : |
#±----------------------------------------------------------------------------/

T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。

本章将分析XQuery的 value() 方法、 exist() 方法和 nodes() 方法

------------------------------value() 方法--------------------------------------

–value(XPath条件,数据类型)：结果为指定的标量值类型; XPath条件结果必须唯一

DECLARE @x XML

SET @x=’

彪

阿彪

流氓

光辉

二辉

流氓

小德

小D

臭流氓

’

–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 =‘a’

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=’’

SELECT @a.exist(’/root[(@Somedate cast as xs:date?) eq xs:date(“2012-01-01”)]’)

–下面的示例与上一示例类似，不同之处在于它具有元素。

SET @a = ‘2002-01-01Z’

SELECT @a.exist(’/Somedate[(text()[1] cast as xs:date ?) = xs:date(“2002-01-01”) ]’)

DECLARE @x1 XML

SELECT @x1 = ‘’

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©;

–扩展 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©;

/**********************************************************

value() 方法 nodes() 方法 exist() 方法的综合应用

**********************************************************/

–1 像下面的脚本，结点下还会用结点的，就要用到 text()

DECLARE @xml XML=N’

bc’;

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,‘ab’ UNION ALL

SELECT 2,‘b’ UNION ALL

SELECT 3,‘d’

SELECT

T.c.query(’.’),

T.c.value(’.’, ‘sysname’)

FROM [#tb] A

CROSS APPLY A.name.nodes(’/r/i’) T©

–3 利用xml 拆分字符串

DECLARE @s VARCHAR(100)=‘1,2,3,4,5,6’

SELECT t.c.value(’.’,‘int’) AS col from

(SELECT CAST(’’+REPLACE(@s,’,’,’’)+’’ AS XML ).query(’.’) AS name) AS a

–4 取任意属性的属性值，这里引入了 sql:variable

DECLARE @xml XML

DECLARE @Price DECIMAL(18 , 2),

@xmlPath VARCHAR(10)= ‘Price2’

SET @xml=’’

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 = ’

’

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 = ’

’

–下面表达式将返回其命名空间 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 , ‘二辉流氓’

UNION ALL

SELECT 2 , ‘彪流氓’

SELECT id ,

data=data.query(’

{sql:column(“id”)}

{/root/name}

{/root/type}

FROM @t

id data

1 1二辉流氓

2 2彪流氓

–根据一个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 = ’

1二辉流氓

2彪流氓

’

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(’’ +REPLACE(CustomerAddress, ’ ‘, ‘’) + ‘’ 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(’.’)

上一篇：十大主流数字货币简介

下一篇：三种常见加密 - 哈希算法/MD5、非对称加密，对称加密

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

sql server XML中value(),exists(),nodes()方法

sql server XML中value(),exists(),nodes()方法

51CTO博客