SQL2005 XML字段常用操作语句

select col.exist('/Root/element') from xmltable

select col.query('/Root/element/node') from xmltable

select col.value('(/Root/element/node)[1]','varchar(100)') from xmltable

select col.value('(/Root/element/node/@att)[1]','varchar(100)') from xmltable

update xmltable set col.modify('insert <node>aaa</node> as last into (/Root/element)[1]')

update xmltable set col.modify('delete (/Root/element/node)[2]')

update xmltable set col.modify('replace value of (/Root/element/node/text())[2] with "ccc"')

 

SELECT  col.value('(.)','varchar(5)') as id

FROM   xmltable

CROSS APPLY xmltable.col.nodes('/Root/element/node') as t(c)

 

select a.logIp,a.TimeStamp,b.Username,c.description

from [log] as a

left join aspnet_Users as b on a.aspnet_users_id=b.aspnet_users_id

inner join aclrule as c on a.aclruleid=c.aclruleid order by a.TimeStamp desc

where a.TableIds.exist('/ids/id[@t="restaurant" and @v="16912"]')=1

 

--这里是将节点id的内容用“,”串联,并转换成varchar显示

select articleid id, title,

stuff(cast(categoryids.query('for $id in //id return concat(",",$id)') as varchar(max)),1,1,'') cids,

    substring(body,

    case when charindex('d',body,0) < 10 then 0

         else charindex('d',body,0) - 10 end ,40) + '...' [body]

    ,'article' [type]

from article

 

-------------------------------------------------------------------

 

id    data
-------------------------------
11    <ids> <id>1 </id> <id>2 <id> </ids>,


==>

id    data
-------------
11      1
11      2

 

DECLARE @a TABLE(id INT,data XML)
INSERT @a SELECT 11,'<ids><id>1</id><id>2</id></ids>'

SELECT a.id,b.id data_id FROM @a a
CROSS APPLY
    (
        SELECT id = t.x.value('.','varchar(100)') FROM a.data.nodes('//id') AS t(x)
    ) b
/*
id          data_id
----------- --------
11          1
11          2

*/

--TRUNCATE TABLE 删除数据并还原Identity field 到0

 

 

 

-- Create primary index.

CREATE PRIMARY XML INDEX PIdx_Log_TableIds

ON [Log](TableIds)

GO

-- Create secondary indexes (PATH, VALUE, PROPERTY).

CREATE XML INDEX PIdx_Log_TableIds_PATH ON [Log](TableIds)

USING XML INDEX PIdx_Log_TableIds

FOR PATH

 

 

 

 

 

**********************************************************************************

 

 

select Tag

                            from

                            (

                                select ltrim(rtrim(t1.Tag.value('.','nvarchar(max)'))) as Tag

                                from

                                (

                                select (cast

                                ('<Tags><Tag>'+replace(replace(replace(replace(Tag,'、','</Tag><Tag>'),',','</Tag><Tag>'),'，','</Tag><Tag>'),

                                '&', '&amp;') +'</Tag></Tags>' as xml)) as Tags

                                from review where review.Status >= 10 and Tag is not null and

                                ltrim(rtrim(Tag)) != ''  and Tag not like  N'%<%' and PoiId=90) a

                                cross apply

                                Tags.nodes('/Tags/Tag/.') as t1(Tag) where

                                ltrim(rtrim(t1.Tag.value('.','nvarchar(max)'))) != ''

                               

                                union all

                               

                                select keyword from PopularTag where [type] = 1 and poiid = 90

                           

                            ) as  c

                           

                            where Tag not in (select keyword from PopularTag where [type] = 2 and poiid = 90)

 

 

 ******************************************************************

 

SELECT  ',' +  keyword

FROM   populartag

FOR XML PATH('')

 

 

-- 将一列数据的内容，合并成一个。

 

select poiid, [values]=stuff((select ','+[keyword] from populartag t where poiid=populartag.poiid

for xml path('')), 1, 1, '')

from populartag

group by poiid