在SQL server中设置某列的值的类型为xml,以下为对该列中的值进行添加/删除XML节点, 修改节点间的字符等
1. 创建数据库表TestProduct, 使列PROD_REPORT_X的类型为xml
create table TestProduct(
PROD_ID_N integer not null,
PROD_REPORT_X XML
)
go
2. 添加一行
insert into TestProduct
Values(1, '<productinfo>
<name>SPF</name>
<spec>
<thickness>2</thickness>
<width>4</width>
</spec>
<grade type="commercial">2BTR</grade>
</productinfo>')
3. 修改列PROD_REPORT_X的值, 在已有的XML中添加一节点。
update TestProduct
Set PROD_REPORT_X.modify('
insert <bf>1000</bf> as first into (/productinfo/spec)[1]')
where PROD_ID_N = 1
4. 修改列PROD_REPORT_X的值, 为Spec节点添加属性type=“internal”。
update TestProduct
Set PROD_REPORT_X.modify('
insert attribute type{"internal"} into (/productinfo/spec)[1]')
where PROD_ID_N = 1
5. 修改列PROD_REPORT_X的值, 修改节点<name></name>之间的文本信息。
update TestProduct
Set PROD_REPORT_X.modify('
replace value of (/productinfo/name/text())[1] with "F/L"')
where PROD_ID_N = 1
6. 修改列PROD_REPORT_X的值, 将<grade>节点的属性type的值修改为Custom。
update TestProduct
set PROD_REPORT_X.modify('
replace value of (/productinfo/grade/@type)[1] with "Custom"')
where PROD_ID_N = 1
7. 修改列PROD_REPORT_X的值, 删除<grade>的属性type。
update TestProduct
Set PROD_REPORT_X.modify('
delete (/productinfo/grade/@type)')
8. 修改列PROD_REPORT_X的值,删除节点<bf>。
update TestProduct
Set PROD_REPORT_X.modify('
delete (/productinfo/spec/bf)[1]')
9. 查询节点<spec> 及其子节点
select PROD_REPORT_X.query('/productinfo/spec')
from TestProduct
where PROD_ID_N = 1
10. 查询节点<width>中得文本信息
select PROD_REPORT_X.value('(/productinfo/spec/width)[1]', 'int') as Width
from TestProduct
where PROD_ID_N = 1
11. 查询节点<spec>的属性值type
select PROD_REPORT_X.value('(/productinfo/spec/@type)[1]', 'varchar(50)') as Width
from TestProduct
where PROD_ID_N = 1
12. 检查<productinfo>节点中是否存在 节点<name>F/L</name>
select *
from TestProduct
where PROD_REPORT_X.exist('/productinfo[name="F/L"]')=1