只整理了一些还不是很完全,以后会继续补全。
--=======
--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.通过变量定位和修改属性