XML操作

 

create table docs
(
 pk int primary key,
 Xcol xml not null
)

declare @xmlDoc xml
set @xmlDoc='<book id="00001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
insert into docs values(1,@xmlDoc)

select * from docs
-- 查询带有标签的数据
select Xcol.query('/book/title') from docs
--查询值
select Xcol.value('(/book/title)[1]','nvarchar(max)') from docs
--查询属性
select Xcol.value('(/book/@id)[1]','nvarchar(max)') from docs

--使用xpath进行查询
declare @xmlDoc xml
set @xmlDoc='<root>
<book id="0001">
<title>C# Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<book id="0002">
<title>Java Program</title>
<author>Tom</author>
<price>49</price>
</book>
</root>'
select @xmlDoc.query('(/root/book[@id="0002"])')

--修改节点的值
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('replace value of(/book[@id=0001]/price/text())[1] with 100')
select @xmlDoc.query('/book[@id=0001]')

-- 删除节点
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('delete/book[@id=0001]')
select @xmlDoc

--添加节点
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('insert <isbn>78-596</isbn> before (/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/book[@id=0001]/isbn)')

--添加属性
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('insert attribute date{"2008-10-1"} into (/book[@id=0001])[1]')
select @xmlDoc

--删除属性
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('delete /book[@id=0001]/@id')
select @xmlDoc

--修改属性
declare @xmlDoc xml
set @xmlDoc='<book id="0001">
<title>C program</title>
<author>David</author>
<price>21</price>
</book>'
set @xmlDoc.modify('replace value of (book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/book)[1]')

-- create Employees table and insert values
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
empid INT NOT NULL,
mgrid INT NULL,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
GO

-- fill datas
INSERT INTO employees VALUES  (1,1,'Nancy',00.00)
INSERT INTO employees VALUES  (2,1,'Andrew',00.00)
INSERT INTO employees VALUES  (3,1,'Janet',00.00)
INSERT INTO employees VALUES  (4,1,'Margaret',00.00)
INSERT INTO employees VALUES  (5,2,'Steven',00.00)
INSERT INTO employees VALUES  (6,2,'Michael',00.00)

select * from Employees for xml auto
SELECT * FROM Employees FOR XML AUTO, ELEMENTS