sql操作xml类型的的数据
查询:
select * from ceshitable where ceshi.value('(ceshi/name)[1]','nvarchar(200)') like '%ceshi9%';
修改:
update ceshitable set ceshi.modify('replace value of (ceshi/name//text())[1] with "修改后的值"') where ceshi.value('(ceshi/id)[1]','int') = 1;
排序:
select top 10 * from ceshitable order by ceshi.value('(ceshi/id)[1]', 'int')
汇总:
select SUM(ceshi.value('(ceshi/id)[1]','int')) from ceshitable where ceshi.value('(ceshi/id)[1]','int') <=10
分组:
select COUNT(*),table1.tName from (
SELECT table2.Loc.value('TitleIntact[1]','varchar(250)') as tName FROM PE_ContentManage_Article
CROSS APPLY ExtendContent.nodes('/ExtendContent') as table2(Loc)) as table1
GROUP by table1.tName