SQL对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]')

eg :  update region set Metadata.modify('insert <HomeRegionId>1006</HomeRegionId> as last into (/)') WHERE RegionId = '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

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

 

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

from [log] as a

leftjoin aspnet_Users as b on a.aspnet_users_id=b.aspnet_users_id

innerjoin aclrule as c on a.aclruleid=c.aclruleid orderby 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)')asvarchar(max)),1,1,'') cids,

    substring(body,

    casewhencharindex('d',body,0)< 10 then 0

         elsecharindex('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@aTABLE(id INT,data XML)
INSERT@aSELECT11,'<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.

CREATEPRIMARYXMLINDEX PIdx_Log_TableIds

ON [Log](TableIds)

GO

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

CREATEXMLINDEX PIdx_Log_TableIds_PATH ON [Log](TableIds)

USING XMLINDEX PIdx_Log_TableIds

FORPATH

 

 

 

 

 

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

 

 

select Tag

                            from

                            (

                                selectltrim(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>'asxml))as Tags

                                from review where review.Status >= 10 and Tag isnotnulland

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

                                crossapply

                                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 notin(select keyword from PopularTag where [type] = 2 and poiid = 90)

 

 

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

 

SELECT  ','+  keyword

FROM   populartag

FORXMLPATH('')

 

 

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

 

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

forxmlpath('')), 1, 1,'')

from populartag

group by poiid