sql XML操作
-=======
--XML
--=======
--1.modify()
DECLARE @x XML
SELECT @x = '<People NAME="dongsheng" SEX="女"/>'
DECLARE @SEX NVARCHAR(10)
SELECT @SEX = '男'
SET @x.modify(' replace value of (/People/@SEX)[1] with sql:variable("@SEX")')
SELECT @x
/*output:<People NAME="dongsheng" SEX="男" />*/
--2.delete()
DECLARE @x XML
SELECT @x = '<People NAME="dongsheng" SEX="男"/>'
SET @x.modify(' delete (/People/@SEX)[1] ')
SELECT @x
/*output:<People NAME="dongsheng" />*/
--3.insert() attribute
DECLARE @x XML
SELECT @x = '<People NAME="dongsheng" />'
DECLARE @SEX VARCHAR(15)
SELECT @SEX = '男'
SET @x.modify(' insert attribute SEX {sql:variable("@SEX")} as last into
(/People)[1]')
SELECT @x
/*output:<People NAME="dongsheng" SEX="男" />*/
--4.insert() element
DECLARE @x XML
SELECT @x ='<People NAME="dongsheng" />'
DECLARE @SEX VARCHAR(15)
SELECT @SEX = '男'
SET @x.modify(' insert element SEX {sql:variable("@SEX")} as last into
(/People)[1]')
SELECT @x
/*
output: <People NAME="dongsheng">
<SEX>男</SEX>
</People>
*/
--5.读取XML
--下面为多种方法从XML中读取EMAIL
DECLARE @x XML
SELECT @x = '
<People>
<dongsheng>
<Info Name="Email">dongsheng@xxyy.com</Info>
<Info Name="Phone">678945546</Info>
<Info Name="qq">36575</Info>
</dongsheng>
</People>'
-- 方法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 =
'<Peoples>
<People Name="tudou" Sex="女" />
<People Name="choushuigou" Sex="女"/>
<People Name="dongsheng" Sex="男" />
</Peoples>'
SELECT
v.value('@Name[1]','VARCHAR(20)') AS Name,
v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/Peoples/People') x(v)
/*
Name Sex
-------------------- --------------------
tudou 女
choushuigou 女
dongsheng 男
(3 行受影响)
*/
--7.多属性过滤
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="1234" dept="IT" type="合同工">
<Info NAME="dongsheng" SEX="男" QQ="5454545454"/>
</Employee>
<Employee id="5656" dept="IT" type="临时工">
<Info NAME="土豆" SEX="女" QQ="5345454554"/>
</Employee>
<Employee id="3242" dept="市场" type="合同工">
<Info NAME="choushuigou" SEX="女" QQ="54543545"/>
</Employee>
</Employees>'
--查询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(10)') AS NAME,
C.value('@SEX[1]','VARCHAR(10)') AS SEX,
C.value('@QQ[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(10)') AS NAME,
C.value('@SEX[1]','VARCHAR(10)') AS SEX,
C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('//Employee[@dept="IT"][@type="合同工"]/*') T(C)
/*
NAME SEX QQ
---------- ---------- --------------------
dongsheng 男 5454545454
*/
--8.通过变量定位和修改属性
DECLARE @x XML
SELECT @x = '
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="3" />
</Variables>
</Root>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'V3'--需要修改的Variable
SELECT @val = '6' --需要修改的值
--定位并且修改
SET @x.modify('
replace value of (
/Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value
)[1]
with sql:variable("@val")
')
SELECT @x
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />--此处VALUE值被修改
</Variables>
</Root>
*/
--9.上面演示如何修改XML变量,下面看看如何修改XML列
DECLARE @t TABLE (data XML)
INSERT INTO @t (data) SELECT '
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="3" />
</Variables>
</Root>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'V3'
SELECT @val = '6'
UPDATE @t
SET data.modify('
replace value of (/Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value)[1]
with sql:variable("@val")
')
SELECT * FROM @t
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />
</Variables>
</Root>
*/
--10.上面根据属性值来定位更新,下面根据属性名称来定位更新
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆" SEX="女" QQ="5345454554"/>
</Peoples>'
DECLARE @attributename VARCHAR(20)
DECLARE @SEX VARCHAR(2)
SELECT @attributename = 'SEX'--需要定位的属性名称
SELECT @SEX = '男' --属性需要更新的新值
SET @x.modify('
replace value of (
/Peoples/People/@*[local-name()=sql:variable("@attributename")]
)[1]
with sql:variable("@SEX")
')
select @x
/*
<Peoples>
<People NAME="土豆" SEX="男" QQ="5345454554" />
</Peoples>
*/
--11.更新元素的值
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>土豆</NAME>
<SEX>男</SEX>
<QQ>5345454554</QQ>
</People>
</Peoples>'
DECLARE @SEX CHAR(2)
SELECT @SEX = '女'
SET @x.modify('
replace value of (/Peoples/People/SEX/text())[1]
with sql:variable("@SEX")' )
SELECT @x
/*
<Peoples>
<People>
<NAME>土豆</NAME>
<SEX>女</SEX>
<QQ>5345454554</QQ>
</People>
</Peoples>
*/
--12.从XML变量中删除元素
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>土豆</NAME>
<SEX>男</SEX>
<QQ>5345454554</QQ>
</People>
</Peoples>'
SET @x.modify('
delete (/Peoples/People/SEX)[1]'
)
SELECT @x
/*
<Peoples>
<People>
<NAME>土豆</NAME>
<QQ>5345454554</QQ>
</People>
</Peoples>
*/
--13.XML运用分割字符串
-- create table
DECLARE @companies Table(
CompanyID INT,
CompanyCodes VARCHAR(100)
)
-- insert data
insert into @companies(CompanyID, CompanyCodes) values(1,'1|2')
insert into @companies(CompanyID, CompanyCodes) values(2,'1|2|3|4')
insert into @companies(CompanyID, CompanyCodes) values(3,'1|2')
-- Query
;WITH cte AS (
SELECT
CompanyID,
CAST('<i>' + REPLACE(CompanyCodes, '|', '</i><i>') + '</i>' AS XML) AS CompanyCodes
FROM @Companies
)
SELECT
CompanyID,
x.i.value('.', 'VARCHAR(10)') AS CompanyCode
FROM cte
CROSS APPLY CompanyCodes.nodes('//i') x(i)
/*
CompanyID CompanyCode
----------- -----------
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
*/
--14.解析对应元素的前一个元素
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆"/>
<People NAME="chouliumang"/>
<People NAME="xiaobiesan"/>
</Peoples>'
select @x.query('
((/Peoples/People)[. << (/Peoples/People[@NAME="xiaobiesan"])[1]])[last()]')
/*
<People NAME="chouliumang" />
*/
--15.解析对应元素的下一个元素
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆"/>
<People NAME="chouliumang"/>
<People NAME="xiaobiesan"/>
</Peoples>'
select @x.query('
((/Peoples/People)[. >> (/Peoples/People[@NAME="土豆"])[1]])[1]')
/*
<People NAME="chouliumang" />
*/
--16.移动元素
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆"/>
<People NAME="chouliumang"/>
<People NAME="xiaobiesan"/>
</Peoples>'
------------------------------------------------------------
-- "xiaobiesan" 向上移动一层
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="xiaobiesan"]
before (/Peoples/People[. << (/Peoples/People[@NAME="xiaobiesan"])[1]])
[last()]
')
SET @x.modify ('
delete /Peoples/People[@NAME="xiaobiesan"]
[. is (/Peoples/People[@NAME="xiaobiesan"])[last()]]
')
SELECT @x
/*
<Peoples>
<People NAME="土豆" />
<People NAME="xiaobiesan" />
<People NAME="chouliumang" />
</Peoples>
*/
------------------------------------------------------------
-- "土豆" 向下移动一层
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="土豆"]
before (/Peoples/People[. >> (/Peoples/People[@NAME="土豆"])[1]])
[last()]
')
SET @x.modify ('
delete (/Peoples/People[@NAME="土豆"])[1]
')
SELECT @x
/*
<Peoples>
<People NAME="xiaobiesan" />
<People NAME="土豆" />
<People NAME="chouliumang" />
</Peoples>
*/
--17.移动属性到对应的元素前
--移动元素
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆"/>
<People NAME="chouliumang"/>
<People NAME="xiaobiesan"/>
<People NAME="chunlv"/>
</Peoples>'
------------------------------------------------------------
-- "xiaobiesan" 向上移动到"土豆"前面
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="xiaobiesan"]
before (/Peoples/People[@NAME="土豆"])[1]
')
SET @x.modify ('
delete (/Peoples/People[@NAME="xiaobiesan"] )[2]
')
SELECT @x
/*
<Peoples>
<People NAME="xiaobiesan" />
<People NAME="土豆" />
<People NAME="chouliumang" />
<People NAME="chunlv" />
</Peoples>
*/
------------------------------------------------------------
-- "土豆" 向下移动到"chunlv"后面
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="土豆"]
after (/Peoples/People[@NAME="chunlv"])[1]
')
SET @x.modify ('
delete (/Peoples/People[@NAME="土豆"])[1]
')
SELECT @x
/*
<Peoples>
<People NAME="xiaobiesan" />
<People NAME="chouliumang" />
<People NAME="chunlv" />
<People NAME="土豆" />
</Peoples>
*/
--移动元素到指定的位置
------------------------------------------------------------
-- "xiaobiesan" 移动到position 3
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="xiaobiesan"]
after (/Peoples/People)[3]
')
SET @x.modify ('
delete (/Peoples/People[@NAME="xiaobiesan"])[1]
')
SELECT @x
/*
<Peoples>
<People NAME="chouliumang" />
<People NAME="chunlv" />
<People NAME="xiaobiesan" />
<People NAME="土豆" />
</Peoples>
*/
------------------------------------------------------------
-- "chouliumang" 移动到最后(last())
------------------------------------------------------------
set @x.modify('
insert /Peoples/People[@NAME="chouliumang"]
after (/Peoples/People)[last()]
')
SET @x.modify ('
delete (/Peoples/People[@NAME="chouliumang"])[1]
')
SELECT @x
/*
<Peoples>
<People NAME="chunlv" />
<People NAME="xiaobiesan" />
<People NAME="土豆" />
<People NAME="chouliumang" />
</Peoples>
*/
--18.查询元素的位置和值
DECLARE @x XML
SELECT @x = '
<Peoples>
<People NAME="土豆"/>
<People NAME="chouliumang"/>
<People NAME="xiaobiesan"/>
<People NAME="chunlv"/>
</Peoples>'
SELECT a.number as position
,b.name
FROM master.dbo.spt_values A
CROSS APPLY (
SELECT C.value('@NAME','varchar(20)') as NAME
FROM @x.nodes('/Peoples/People[position()=sql:column("number")]')T(C)) b
WHERE A.type='P'
/*
position name
----------- --------------------
1 土豆
2 chouliumang
3 xiaobiesan
4 chunlv
(4 行受影响)
*/
--19.读取指定变量元素的值
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
<QQ>423545</QQ>
</People>
<People>
<NAME>土豆</NAME>
<SEX>男</SEX>
<QQ>123133</QQ>
</People>
<People>
<NAME>choushuigou</NAME>
<SEX>女</SEX>
<QQ>54543545</QQ>
</People>
</Peoples>
'
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 = '<People>dongsheng</People>'
SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People --星号*代表一个元素
/*
People
--------------------
dongsheng
*/
--读取第二层元素的值
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
<QQ>423545</QQ>
</People>'
SELECT
@x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME
/*
NAME
--------------------
dongsheng
*/
--读取第二个子元素的值
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
<QQ>423545</QQ>
</People>'
SELECT
@x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX
/*
SEX
--------------------
男
*/
--读取所有第二层子元素值
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
<QQ>423545</QQ>
</People>'
SELECT
C.value('.','VARCHAR(20)') AS value
FROM @x.nodes('/*/*') T(C)
/*
value
--------------------
dongsheng
男
423545
*/
--21.使用通配符读取元素名称
DECLARE @x XML
SELECT @x = '<People>dongsheng</People>'
SELECT
@x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
/*
ElementName
--------------------
People
*/
--读取根下第一个元素的名称和值
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
</People>'
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 = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
</People>'
SELECT
@x.value('local-name((/*/*)[2])','VARCHAR(20)') AS ElementName,
@x.value('(/*/*/text())[2]','VARCHAR(20)') AS ElementValue
/*
ElementName ElementValue
-------------------- --------------------
SEX 男
*/
--读取根下所有的元素名称和值
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
</People>'
SELECT
C.value('local-name(.)','VARCHAR(20)') AS ElementName,
C.value('.','VARCHAR(20)') AS ElementValue
FROM @x.nodes('/*/*') T(C)
/*
ElementName ElementValue
-------------------- --------------------
NAME dongsheng
SEX 男
*/
---22.查询元素数量
--如下Peoples根节点下有个People子节点。
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>dongsheng</NAME>
<SEX>男</SEX>
</People>
<People>
<NAME>土豆</NAME>
<SEX>男</SEX>
</People>
<People>
<NAME>choushuigou</NAME>
<SEX>女</SEX>
</People>
</Peoples>
'
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 = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>
</Employees>'
--查询跟节点的属性数量
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 = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>
</Employees>'
--返回第一个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
--------------------
*/
--返回第二个元素的第四个属性值
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
--------------------
*/
--25.判断是XML中否存在相应的属性
DECLARE @x XML
SELECT @x = '<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>'
IF @x.exist('/Employee/@NAME') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--传递变量判断是否存在
DECLARE @x XML
SELECT @x = '<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>'
DECLARE @att VARCHAR(20)
SELECT @att = 'QQ'
IF @x.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--26.循环遍历元素的所有属性
DECLARE @x XML
SELECT @x = '<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>'
DECLARE
@cnt INT,
@totCnt INT,
@attName VARCHAR(30),
@attValue VARCHAR(30)
SELECT
@cnt = 1,
@totCnt = @x.value('count(/Employee/@*)','INT')--获得属性总数量
-- loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@attName = @x.value(
'local-name((/Employee/@*[position()=sql:variable("@cnt")])[1])',
'VARCHAR(30)'),
@attValue = @x.value(
'(/Employee/@*[position()=sql:variable("@cnt")])[1]',
'VARCHAR(30)')
PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
PRINT 'Attribute Name: ' + @attName
PRINT 'Attribute Value: ' + @attValue
PRINT ''
-- increment the counter variable
SELECT @cnt = @cnt + 1
END
/*
Attribute Position: 1
Attribute Name: NAME
Attribute Value: 土豆
Attribute Position: 2
Attribute Name: SEX
Attribute Value: 女
Attribute Position: 3
Attribute Name: QQ
Attribute Value: 5345454554
Attribute Position: 4
Attribute Name: TEL
Attribute Value: 13954697895
*/
--27.返回指定位置的子元素
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>
</Employees>'
SELECT @x.query('(/Employees/Employee)[1]')
/*
<Employee NAME="dongsheng" SEX="男" QQ="5454545454" />
*/
SELECT @x.query('(/Employees/Employee)[position()=2]')
/*
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895" />
*/
--通过变量获取指定位置的子元素
DECLARE @i INT
SELECT @i = 2
SELECT @x.query('(/Employees/Employee)[sql:variable("@i")]')
--or
SELECT @x.query('(/Employees/Employee)[position()=sql:variable("@i")]')
/*
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895" />
*/
--28.循环遍历获得所有子元素
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>
</Employees>'
DECLARE
@cnt INT,
@totCnt 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: <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
Processing Child Element: 2
Child element: <Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>
