use TestDB

go

create Table TbFroXML(id uniqueidentifier primary key,name nvarchar(50),[type] nvarchar(50))

go

insert into TbFroXML values(NEWID(),'BBen','MG')

insert into TbFroXML values(NEWID(),'BB','MG')

insert into TbFroXML values(NEWID(),'Olive','MG')

insert into TbFroXML values(NEWID(),'今天','NMG')

insert into TbFroXML values(NEWID(),'明天','NMG')

insert into TbFroXML values(NEWID(),'未来','NMG')

insert into TbFroXML(id,name) values(newid(),'笨笨')

go

--无名称列，针对行集合中的每一行生成一个对应的row元素，格式：<row>1</row>

select 1 for xml path

--生产格式<row><name> XXX</name></row>

select name from TbFroXML for xml path

--去掉name标签，只剩下row标签，格式：<row>BBen</row><row>BB></row><row>Olive</row>....

select name+'' from TbFroXML for xml path

--只生成要查询的列的数据，去掉row元素，格式：BB未来明天今天BBenOlive

select name+'' from TbFroXML for xml path('')

--生成格式：<row id='XXXXXXX'><name>BBen</name></row>

select id as '@id',name from TbFroXML for xml path

--以NMG标记作为行标记，格式：<NMG><name>BBen</name></NMG><NMG><name>未来</name></NMG>

select name from TbFroXML for xml path('NMG')

--以NMG标记作为行标记，以Mg标记作为内标记，格式：<NMG><MG>BB</MG><NMG><NMG><MG>未来</MG></NMG>

select name as MG from TbFroXML  for xml path('NMG')

--生成格式：<row><BBen><Olive>BB</Olive></BBen></row>

select name as 'BBen/Olive' from TbFroXML for xml path

--生成格式：<row id='*****'>BB></row><row id='*******'>未来</row>

select id as '@id',name as '*' from TbFroXML for xml path

--被指定为列名的路径为data()，则在生成的XML 中，该值将被作为一个原子值来处理,

--生成格式：<row id='*****' name="BB"><name>BB</name>MG</row>

select id as '@id',name as '@name',name,[TYPE] as 'data()' from TbFroXML for xml path

--默认情况下，列中的Null 值映射为“缺少相应的属性、节点或元素，使用ELEMENTS 指令请求以元素为中心的XML 并指定XSINIL 来请求为NULL 值添加元素，格式：<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="7B890E0B-C470-4E93-89A4-3041B70E8DF3"><xx>    <null xsi:nil="true" /><name>笨笨</name><type xsi:nil="true" /></xx></row>

select id as '@id',null as 'xx/null',name as 'xx/name',[TYPE] as 'xx/type' from TbFroXML for xml path ,elements xsinil

--ROOT('oo'),--指定向产生的XML 中添加单个*元素

select id as '@id' ,name from TbFroXML for xml path,root('oo')

--auto模式，格式：<TbFroXML id="41D6A175-C079-4861-9C75-2EE48A62C3BC" name="BB" type="MG" />

select * from TbFroXML for  xml auto

--elements选项,将每列值都映射为〈row>元素的子元素，格式：<TbFroXML><id>41D6A175-C079-4861-9C75-2EE48A62C3BC</id><name>BB</name><type>MG</type></TbFroXML>

select * from TbFroXML for xml auto,elements

--row 模式：格式：<row><id>7B890E0B-C470-4E93-89A4-3041B70E8DF3</id><name>笨笨</name><type xsi:nil="true" /></row>

select * from TbFroXML for xml raw,elements

--elements选项，值为XSINIL 时，将结果集中的值为null 的列映射为xsi：nil=true属性的元素,格式：<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><id>41D6A175-C079-4861-9C75-2EE48A62C3BC</id><name>BB</name><type>MG</type></row>

select * from TbFroXML for xml raw, elements xsinil

--xmldata:结果请求架构

select * from TbFroXML for xml raw,xmldata

--xmlschema：结果请求架构

select * from TbFroXML for xml raw,xmlschema

select 1 as Tag,

NULL as Parent,

3 as [node!1]  --[node!1]代表的是根节点，node为节点名，代表节点层次结构的节点级别

for xml explicit

select 1 as Tag,

null as Parent,

Null as [root!1],

null as [node!2!id!element]--[node!2!id!ELEMENT]node 代表的是节点名称;2代表的节点层次;id 表示的是元素名称;.ELEMENT 选项表示向<node> 元素添加了<id> 元素子级，而不是添加属性。

union all

select 2 as Tag,

1 as Parent,

null,

id

from TbFroXML where id!=null for xml explicit

DECLARE @dataSource XML

SET @dataSource='

<root>

<info id="1">

<user uid="001">

<name>笨笨</name>

<type>MG</type>

</user>

</info>

<info id="2">

<user uid="002">

<name>Olive</name>

<type>MG</type>

</user>

</info>

<info id="3">

<user uid="003">

<name>墨遥</name>

<type>NMG</type>

</user>

</info>

</root>'

--获取root所有子节点

select @dataSource.query('.')

select @dataSource.query('root')

select @dataSource.query('/root')

----获取所有的info节点

select @dataSource.query('//info')

----获取所有info节点下user节点的name节点

select @dataSource.query('//info/user/name')

----获取所有uid>1的所有Info节点

select @dataSource.query('/root/info[./user[@uid>1]]')

if then else 表达式

select @dataSource.query('if (1=3) then /root/info[./user[uid=1]] else /root/info[./user[name="Olive"]]')

--路径表达式步骤中的谓词,获取第一个info节点下的user节点下的name

select @dataSource.query('/root/info[1]/user/name')

--聚合函数

select @dataSource.query('count(/root/info[./user[type="MG"]])')

--FLWOR语法：For、Let、Where、Order by、Return

select @dataSource.query('<result>{for $li in /root/info/user/name[1] return string($li)}</result>')

select @dataSource.query('<result>{for $li in /root/info,$lii in $li/user/name[1] return string($lii)}</result>')

select @dataSource.query('<result>{for $li in /root/info/user order by $li/@uid descending return $li}</result>')

select @dataSource.query('<result>{for $li in /root/info/user order by local-name($li) return $li}</result>')

--value()获取某一节点或其属性的值，然后将其赋值给nvarchar类型

select @dataSource.value('(/root/info/user[1]/name)[1]','nvarchar(20)')

select @dataSource.exist('/root/info/user/name[text()[1]="笨笨"]')

--将日期类型的节点属性转换为日期类型再与对应的值进行比较

declare @date xml

set @date='<root date="2013-10-07"/>'

--exist()

select @date.exist('/root[(@date cast as xs:date?) eq xs:date("2013-10-07")]')

--将日期类型的节点的值转换为日期类型再与对应的值进行比较，date[text()[1] cast xs:date?，将节点值转换为日期类型

declare @date1 xml

set @date1='<root><date>2013-10-07</date></root>'

select @date1.exist('/root/date[(text()[1] cast as xs:date?) eq xs:date("2013-10-07")]')

--取任意属性的值：sql:variable("@attriname")

declare @attr nvarchar(20)='uid'

if @dataSource.exist('/root/info/user/@*[local-name()=sql:variable("@attr")]')>=1

select 'OK'

else select @dataSource.query('/root/info/user/name[local-name()="笨笨"]')

--nodes()方法，将一个xquery表达式拆分成多行

select T.c.query('.') as result  from @dataSource.nodes('/root/info') as T(c)

select T.c.query('.') as result  from @dataSource.nodes('/root/info/user') as T(c)

select T.c.value('(@uid)[1]','varchar(10)') as id,

T.c.value('(./name)[1]','nvarchar(20)') as name,

T.c.value('(./type)[1]','nvarchar(20)') as [type]

from @dataSource.nodes('/root/info/user') T(c)

--第一个value方法获取b节点下的值:cec,第二个value方法获取b节点下的值：c

declare @xml xml='<root><a><b>c<d>e</d>c</b></a></root>'

select @xml.value('(/root/a/b)[1]','nvarchar(20)'),@xml.value('(/root/a/b/text())[1]','nvarchar(20)')

--组合使用

declare @xml xml ='<root><info id="001" name="Olive" type="MG"/></root>'

declare @pos int=2

select @xml.value('local-name((/root/info/@*[position()=sql:variable("@pos")])[1])','nvarchar(20)')

--sql:column:将普通数据列和Xml数据合并

declare @tb table (id int,data xml)

insert into @tb(id,data) select 1,'<root><info><name>Benben</name><type>MG</type></info></root>'

select id,data=data.query('<root><info><id>{sql:column("id")}</id>{/root/info/name}{/root/info/type}</info></root>') from @tb

---contains：模糊查询contains(.,'XX')

select t.c.query('.') from @dataSource.nodes('/root/info/user[./name[contains(.,"笨")]]') t(c)

--在某一节点下添加一个子节点insert into，as first/as last 指定节点插入的位置

set @dataSource.modify('insert <sex>F</sex>  as last into (/root/info/user)[1]')

select @dataSource

--添加某一节点的同级节点，before/after 添加同级节点

set @dataSource.modify('insert <UID>A1</UID> before (/root/info/user/name)[1]')

select @dataSource

--插入属性

declare @a int=111

set @dataSource.modify('insert (attribute a {sql:variable("@a")},

attribute b {".3"})

into (/root/info/user[@uid=001])[1]')

select @dataSource

--删除属性

set @dataSource.modify('delete /root/info/user/@uid')

select @dataSource

--删除节点

set @dataSource.modify('delete /root/info/user/type')

select @dataSource

--删除节点内容

set @dataSource.modify('delete /root/info/user/type/text()')

select @dataSource

--删除所有属性为空的节点

set @dataSource.modify('delete //*[empty(./*)]')

select @dataSource

--修改节点值

set @dataSource.modify('replace value of (/root/info/user/name/text())[1] with "小笨笨"')

select @dataSource

--修改属性值

set @dataSource.modify('replace value of (/root/info/user/@uid)[1] with "0001"')

select @dataSource