sql XML操作(这是网上搜的，感谢整理的人。也留着以后万一用得着的时候)

来源：互联网发布：一搜网络个人巨牌江苏编辑：程序博客网时间：2024/05/13 00:05

--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">

</People>

--5.读取XML

--下面为多种方法从XML中读取EMAIL

DECLARE @x XML

SELECT @x = '

<Info Name="Email">dongsheng@xxyy.com</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>

</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 = '

</Employee>

</Employee>

</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>

</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>

<Variable VariableName="V3" Value="6" />--此处VALUE值被修改

</Variables>

</Root>

--9.上面演示如何修改XML变量,下面看看如何修改XML列

DECLARE @t TABLE (data XML)

INSERT INTO @t (data) SELECT '

<Root>

</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>

</Root>

--10.上面根据属性值来定位更新,下面根据属性名称来定位更新

DECLARE @x XML

SELECT @x = '

</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>

--11.更新元素的值

DECLARE @x XML

SELECT @x = '

</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

</People>

</Peoples>

--12.从XML变量中删除元素

DECLARE @x XML

SELECT @x = '

</People>

</Peoples>'

SET @x.modify('

delete (/Peoples/People/SEX)[1]'

)

SELECT @x

</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>'

select @x.query('

((/Peoples/People)[. << (/Peoples/People[@NAME="xiaobiesan"])[1]])[last()]')

--15.解析对应元素的下一个元素

DECLARE @x XML

SELECT @x = '

</Peoples>'

select @x.query('

((/Peoples/People)[. >> (/Peoples/People[@NAME="土豆"])[1]])[1]')

--16.移动元素

DECLARE @x XML

SELECT @x = '

</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>

------------------------------------------------------------

-- "土豆" 向下移动一层

------------------------------------------------------------

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>

--17.移动属性到对应的元素前

--移动元素

DECLARE @x XML

SELECT @x = '

</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>

------------------------------------------------------------

-- "土豆" 向下移动到"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>

--移动元素到指定的位置

------------------------------------------------------------

-- "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>

------------------------------------------------------------

-- "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>

--18.查询元素的位置和值

DECLARE @x XML

SELECT @x = '

</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 = '

<NAME>dongsheng</NAME>

</People>

</People>

<NAME>choushuigou</NAME>

</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 = '

<NAME>dongsheng</NAME>

</People>'

SELECT

@x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME

NAME

--------------------

dongsheng

--读取第二个子元素的值

DECLARE @x XML

SELECT @x = '

<NAME>dongsheng</NAME>

</People>'

SELECT

@x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX

SEX

--------------------

男

--读取所有第二层子元素值

DECLARE @x XML

SELECT @x = '

<NAME>dongsheng</NAME>

</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 = '

<NAME>dongsheng</NAME>

</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 = '

<NAME>dongsheng</NAME>

</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 = '

<NAME>dongsheng</NAME>

</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 = '

<NAME>dongsheng</NAME>

</People>

</People>

<NAME>choushuigou</NAME>

</People>

</Peoples>

SELECT @x.value('count(/Peoples/People)','INT') AS Children

Children

-----------

--如下Peoples根节点下第一个子节点People下子节点的数量

SELECT @x.value('count(/Peoples/People[1]/*)','INT') AS Children

Children

-----------

--某些时候我们可能不知道根节点和子节点的名称，可以用通配符来代替。

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>'

--查询跟节点的属性数量

SELECT @x.value('count(/Employees/@*)','INT') AS AttributeCountOfRoot

AttributeCountOfRoot

--------------------

--第一个Employee节点的属性数量

SELECT @x.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCountOfFirstElement

AttributeCountOfFirstElement

----------------------------

--第二个Employee节点的属性数量

SELECT @x.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCountOfSeconfElement

AttributeCountOfSeconfElement

-----------------------------

--如果不清楚节点名称可以用*通配符代替

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

--------------

--24.返回给定位置的属性值或者名称

DECLARE @x XML

SELECT @x = '

</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>'

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

</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"/>

0 0