XML操作
来源:互联网 发布:淘宝上比较好的饰品店 编辑:程序博客网 时间:2024/06/06 01:44
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
- 操作XML
- XML操作
- xml操作
- 操作XML
- xml 操作
- XML操作
- xml操作
- xml操作
- 操作XML
- XML 操作
- 操作XML
- XML操作
- XML 操作
- xml操作
- Xml操作
- xml操作
- xml操作
- xml操作
- 软件架构模式
- 删除日志
- 管理你的老板
- 软件架构模式的种类
- Excel操作
- XML操作
- oracle sqldeveloper客户端工具连接sql,mysql数据库
- 文件上传和下载
- JQueryMobile开发中遇到问题的解决办法
- Excel导出
- 查看文件的MD5/SHA1效验值
- 发布Hourlog 0.02版: 给日期添加JSCalendar功能
- Shell 循环语句用法小结
- Windows Phone开发(4):框架和页