‘
— 方法1
SELECT @x.value(‘data(/People/dongsheng/Info[@Name=”Email”])[1]’,’varchar(30)’)
— 方法2
SELECT @x.value(‘(/People/dongsheng/Info[@Name=”Email”])[1]’,’varchar(30)’)
— 方法3
SELECT
C.value(‘.’,’varchar(30)’)
FROM @x.nodes(‘/People/dongsheng/Info[@Name=”Email”]’) T(C)
— 方法4
SELECT
C.value(‘(Info[@Name=”Email”])[1]’,’varchar(30)’)
FROM @x.nodes(‘/People/dongsheng’) T(C)
— 方法5
SELECT
C.value(‘(dongsheng/Info[@Name=”Email”])[1]’,’varchar(30)’)
FROM @x.nodes(‘/People’) T(C)
— 方法6
SELECT
C.value(‘.’,’varchar(30)’)
FROM @x.nodes(‘/People/dongsheng/Info’) T(C)
WHERE C.value(‘(.[@Name=”Email”])[1]’,’varchar(30)’) IS NOT NULL
— 方法7
SELECT
C.value(‘.’,’varchar(30)’)
FROM @x.nodes(‘/People/dongsheng/Info’) T(C)
WHERE C.exist(‘(.[@Name=”Email”])[1]’) = 1
–6.Reading values from an XML variable
DECLARE @x XML
SELECT @x =
‘
‘
SELECT
v.value(‘@Name[1]’,’VARCHAR(20)’) AS Name,v.value(‘@Sex[1]’,’VARCHAR(20)’) AS Sex
FROM @x.nodes(‘/Peoples/People’) x(v)
–7.多属性过滤
DECLARE @x XML
SELECT @x = ‘
‘
–查询dept为IT的人员信息
–方法1
SELECT
C.value(‘@NAME[1]’,’VARCHAR(10)’) AS NAME,C.value(‘@SEX[1]’,’VARCHAR(10)’) AS SEX,C.value(‘@QQ[1]’,’VARCHAR(20)’) AS QQ
FROM @x.nodes(‘/Employees/Employee[@dept=”IT”]/Info’) T(C)
/*
NAME SEX QQ
———- ———- ——————–
dongsheng 男 5454545454
土豆 女 5345454554
*/
–方法2
SELECT
C.value(‘@NAME[1]’,’VARCHAR(20)’) AS QQ
FROM @x.nodes(‘//Employee[@dept=”IT”]/*’) T(C)
/*
NAME SEX QQ
———- ———- ——————–
dongsheng 男 5454545454
土豆 女 5345454554
*/
–查询出IT部门type为Permanent的员工
SELECT
C.value(‘@NAME[1]’,’VARCHAR(20)’) AS QQ
FROM @x.nodes(‘//Employee[@dept=”IT”][@type=”合同工”]/*’) T(C)
/*
NAME SEX QQ
———- ———- ——————–
dongsheng 男 5454545454
*/
–12.从XML变量中删除元素
DECLARE @x XML
SELECT @x = ‘
土豆
男
5345454554
‘
SET @x.modify(‘
delete (/Peoples/People/SEX)[1]’
)
SELECT @x
/*
土豆
5345454554
*/
–19.读取指定变量元素的值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
423545
土豆
男
123133
choushuigou
女
54543545
‘
DECLARE @ElementName VARCHAR(20)
SELECT @ElementName = ‘NAME’
SELECT c.value(‘.’,’VARCHAR(20)’) AS NAME
FROM @x.nodes(‘/Peoples/People/*[local-name()=sql:variable(“@ElementName”)]’) T(C)
/*
NAME
——————–
dongsheng
土豆
choushuigou
*/
–20使用通配符读取元素值
–读取根元素的值
DECLARE @x1 XML
SELECT @x1 = ‘
dongsheng‘
SELECT @x1.value(‘(/*/text())[1]’,’VARCHAR(20)’) AS People –星号*代表一个元素
/*
People
——————–
dongsheng
*/
–读取第二层元素的值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
423545
‘
SELECT
@x.value(‘(/*/*/text())[1]’,’VARCHAR(20)’) AS NAME
/*
NAME
——————–
dongsheng
*/
–读取第二个子元素的值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
423545
‘
SELECT
@x.value(‘(/*/*/text())[2]’,’VARCHAR(20)’) AS SEX
/*
SEX
——————–
男
*/
–读取所有第二层子元素值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
423545
‘
SELECT
C.value(‘.’,’VARCHAR(20)’) AS value
FROM @x.nodes(‘/*/*’) T(C)
/*
value
——————–
dongsheng
男
423545
*/
–21.使用通配符读取元素名称
DECLARE @x XML
SELECT @x = ‘
dongsheng‘
SELECT
@x.value(‘local-name(/*[1])’,’VARCHAR(20)’) AS ElementName
/*
ElementName
——————–
People
*/
–读取根下第一个元素的名称和值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
‘
SELECT
@x.value(‘local-name((/*/*)[1])’,’VARCHAR(20)’) AS ElementName,@x.value(‘(/*/*/text())[1]’,’VARCHAR(20)’) AS ElementValue
/*
ElementName ElementValue
——————– ——————–
NAME dongsheng
*/
–读取根下第二个元素的名称和值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
‘
SELECT
@x.value(‘local-name((/*/*)[2])’,@x.value(‘(/*/*/text())[2]’,’VARCHAR(20)’) AS ElementValue
/*
ElementName ElementValue
——————– ——————–
SEX 男
*/
–读取根下所有的元素名称和值
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
‘
SELECT
C.value(‘local-name(.)’,C.value(‘.’,’VARCHAR(20)’) AS ElementValue
FROM @x.nodes(‘/*/*’) T(C)
/*
ElementName ElementValue
——————– ——————–
NAME dongsheng
SEX 男
*/
—22.查询元素数量
–如下Peoples根节点下有个People子节点。
DECLARE @x XML
SELECT @x = ‘
dongsheng
男
土豆
男
choushuigou
女
‘
SELECT @x.value(‘count(/Peoples/People)’,’INT’) AS Children
/*
Children
———–
3
*/
–如下Peoples根节点下第一个子节点People下子节点的数量
SELECT @x.value(‘count(/Peoples/People[1]/*)’,’INT’) AS Children
/*
Children
———–
2
*/
–某些时候我们可能不知道根节点和子节点的名称,可以用通配符来代替。
SELECT @x.value(‘count(/*/*)’,’INT’) AS ChildrenOfRoot,@x.value(‘count(/*/*[1]/*)’,’INT’) AS ChildrenOfFirstChildElement
/*
ChildrenOfRoot ChildrenOfFirstChildElement
————– —————————
3 2
*/
–23.查询属性的数量
DECLARE @x XML
SELECT @x = ‘
‘
–查询跟节点的属性数量
SELECT @x.value(‘count(/Employees/@*)’,’INT’) AS AttributeCountOfRoot
/*
AttributeCountOfRoot
——————–
1
*/
–第一个Employee节点的属性数量
SELECT @x.value(‘count(/Employees/Employee[1]/@*)’,’INT’) AS AttributeCountOfFirstElement
/*
AttributeCountOfFirstElement
—————————-
3
*/
–第二个Employee节点的属性数量
SELECT @x.value(‘count(/Employees/Employee[2]/@*)’,’INT’) AS AttributeCountOfSeconfElement
/*
AttributeCountOfSeconfElement
—————————–
4
*/
–如果不清楚节点名称可以用*通配符代替
SELECT @x.value(‘count(/*/@*)’,’INT’) AS AttributeCountOfRoot,@x.value(‘count(/*/*[1]/@*)’,’INT’) AS AttributeCountOfFirstElement,@x.value(‘count(/*/*[2]/@*)’,’INT’) AS AttributeCountOfSeconfElement
/*
AttributeCountOfRoot AttributeCountOfFirstElement AttributeCountOfSeconfElement
——————– —————————- —————————–
1 3 4
*/
–返回没个节点的属性值
SELECT C.value(‘count(./@*)’,’INT’) AS AttributeCount
FROM @x.nodes(‘/*/*’) T(C)
/*
AttributeCount
————–
3
4
*/
–24.返回给定位置的属性值或者名称
DECLARE @x XML
SELECT @x = ‘
‘
–返回第一个Employee节点的第一个位置的属性值
SELECT @x.value(‘(/Employees/Employee[1]/@*[position()=1])[1]’,’VARCHAR(20)’) AS AttValue
/*
AttValue
——————–
dongsheng
*/
–返回第二个Employee节点的第四个位置的属性值
SELECT @x.value(‘(/Employees/Employee[2]/@*[position()=4])[1]’,’VARCHAR(20)’) AS AttValue
/*
AttValue
——————–
13954697895
*/
–返回第一个元素的第三个属性值
SELECT @x.value(‘local-name((/Employees/Employee[1]/@*[position()=3])[1])’,’VARCHAR(20)’) AS AttName
/*
AttName
——————–
QQ
*/
–返回第二个元素的第四个属性值
SELECT @x.value(‘local-name((/Employees/Employee[2]/@*[position()=4])[1])’,’VARCHAR(20)’) AS AttName
/*
AttName
——————–
TEL
*/
–通过变量传递位置返回属性值
DECLARE @Elepos INT,@Attpos INT
SELECT @Elepos=2,@Attpos = 3
SELECT @x.value(‘local-name((/Employees/Employee[sql:variable(“@Elepos”)]/@*[position()=sql:variable(“@Attpos”)])[1])’,’VARCHAR(20)’) AS AttName
/*
AttName
——————–
QQ
*/
–25.判断是XML中否存在相应的属性
DECLARE @x XML
SELECT @x = ‘‘
SELECT @x.query(‘(/Employees/Employee)[1]’)
/*
*/
SELECT @x.query(‘(/Employees/Employee)[position()=2]’)
/*
*/
–通过变量获取指定位置的子元素
DECLARE @i INT
SELECT @i = 2
SELECT @x.query(‘(/Employees/Employee)[sql:variable(“@i”)]’)
–or
SELECT @x.query(‘(/Employees/Employee)[position()=sql:variable(“@i”)]’)
/*
*/
–28.循环遍历获得所有子元素
DECLARE @x XML
SELECT @x = ‘
‘
DECLARE
@cnt INT,@child XML
— counter variables
SELECT
@cnt = 1,@totCnt = @x.value(‘count(/Employees/Employee)’,’INT’)
— loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@child = @x.query('/Employees/Employee[position()=sql:variable("@cnt")]')
PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
PRINT 'Child element: ' + CAST(@child AS VARCHAR(100))
PRINT ''
-- incremet the counter variable
SELECT @cnt = @cnt + 1
END
/*
Processing Child Element: 1
Child element:
SQL Server 中对XML数据的五种基本操作