By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .
access_time 11 years ago visibility396 comment 0 language中文


1. query()方法:返回满足条件的所有XML行。只能用于SELECT子句当中。

2. value()方法:返回从XML节点中提取的标量值。必须在value()方法的第二个参数中指定所返回的标量的数据类型,所以value()方法可以与其它标量进行比较。可用于SELECT子句和WHERE子句。

3. exist()方法:返回int型标量的0或者1。对每行的XML数据类型进行存在性检查。可用于SELECT子句和WHERE子句。

4. nodes()方法:返回只有一个栏位的table,且该table的栏位是XML数据类型。所以nodes()方法只能出现在FROM子句中。



SELECT pk, xCol.query('/doc[@id = 123]//section')
FROM docs


SELECT xCol.value('data((/doc//section[@num = 3]/heading)[1])', 'nvarchar(max)')
FROM docs


SELECT xCol.query('/doc[@id = 123]//section')
FROM   docs
WHERE xCol.exist ('/doc[@id = 123]') = 1


SELECT nref.value('@genre', 'varchar(max)') LastName
FROM   docs CROSS APPLY xCol.nodes('//book') AS R(nref)





UPDATE docs SET xCol.modify('
<section num="2">
after (/doc/section[@num=1])[1]')


UPDATE XmlCatalog
SET    Document.modify ('
default namespace = "http://myBooks"
replace value of (/bookstore/book[@ISBN=
"1-8610-0311-0"]/price)[1] with 49.99')



sql:variable()方法:可以在XQuery或XML DML表达式中应用SQL变量的值。


DECLARE @isbn varchar(20)
SET     @isbn = '0-7356-1588-2'
FROM    docs
WHERE   xCol.exist ('/book[@ISBN = sql:variable("@isbn")]') = 1

sql:column()方法:可以在XQuery或XML DML表达式中使用来自关系列的值。


create table #Test(xmlColumn xml)
insert #Test values('<book>
<b1 id="b001">aaaaa</b1>
<b2 id="b002">bbbbb</b2>
<b3 id="b003">ccccc</b3>
select * from #Test
update #Test set xmlColumn.modify('replace value of (/book/b1/@id)[1] with "新的id"')
update #Test set xmlColumn.modify('insert attribute price{"价格"} into (/book/b1)[1]')
select * from #Test


包括SQl server xml列中:

A. 将元素节点插入文档中

B. 将多个元素插入文档中

C. 将属性插入文档中

D. 插入注释节点

E. 插入处理指令

F. 使用 CDATA 部分插入数据

G. 插入文本节点

H. 将新元素插入非类型化的 xml 列

I. 根据 if 条件语句进行插入

J. 将节点插入类型化的 xml 列中

info Last modified by Raymond at 6 years ago * This page is subject to Site terms.

More from Kontext

visibility 13649
thumb_up 0
access_time 6 years ago

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will onl...

open_in_new View open_in_new SQL Server

visibility 684
thumb_up 0
access_time 6 years ago

SQL Server provides a batch of great features to build robust, high-performance and scalable data solutions. ...

open_in_new View open_in_new SQL Server

visibility 1143
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario In the previous articles of this serial, I’ve introduced how to use SQL Server as database for online transaction proces...

open_in_new View open_in_new SQL Server

visibility 4114
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...

open_in_new View open_in_new SQL Server

info About author

Kontext dark theme mode

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward
Kontext Column

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us