SQL SERVER XML 学习小结

2013-10-09

SQLSERVERXML学习总结SQLSERVERXML学习总结最新的项目任务要做一个数据同步的功能，这些天都在做技术准备，

SQL SERVER XML 学习总结

SQL SERVER XML 学习总结
最新的项目任务要做一个数据同步的功能，这些天都在做技术准备，主要是用到了微软的Service Broker技术，在熟悉使用该技术的同时，又用到了Sql server xml的一些知识，这两天在家，把这个sql xml的一些知识给学习了一下，下边是一些学习过程中的总结，希望能给大家带来一些帮助。
在学习过程中主要参考(http://blog.csdn.net/beirut/article/details/8154102).
测试用的数据
use TestDB
go
create Table TbFroXML(id uniqueidentifier primary key,name nvarchar(50),[type] nvarchar(50))
go

insert into TbFroXML values(NEWID(),'BBen','MG')
表达式
描述
nodename
选取此节点的所有子节点。
/
从根节点选取。
//
从匹配选择的当前节点选择文档中的节点，而不考虑它们的位置。
.
选取当前节点。
..
选取当前节点的父节点。
@
选取属性。
4.2 谓语（Predicates）
谓语用来查找某个特定的节点或者包含某个指定的值的节点。
谓语被嵌在方括号中。
例： /root/Info/user[1]
4.3 选取未知节点
XPath 通配符可用来选取未知的 XML 元素。
通配符
描述
*
匹配任何元素节点。
@*
匹配任何属性节点。
node()
匹配任何类型的节点。
例：/root/info/*     info节点下的所有元素节点
/root/info/@*   info 节点下所有的属性节点
//user[@]      所有带有属性的user节点
4.4 选取若干路径
通过在路径表达式中使用“|”运算符，您可以选取若干个路径。
例：//User | /root/Info/@id
5.    XQuery5.1 基本的查询
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>')
5.2 Value()
--value()获取某一节点或其属性的值，然后将其赋值给nvarchar类型
select @dataSource.value('(/root/info/user[1]/name)[1]','nvarchar(20)')
5.3 Exist()
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()="笨笨"]')

5.4 Nodes()
--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)
5.5 Modify()5.5.1 Insert
--在某一节点下添加一个子节点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

5.5.2 Delete
--删除属性
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

5.5.3 Replace
--修改节点值
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

热点排行