SQLSERVERXML学习总结.docx

SQL SERVER XML学习总结最新的项目任务要做一个数据同步的功能，这些天都在做技术准备，主要是用到了微软的Service Broker技术，在熟悉使用该技术的同时，又用到了Sql server xml的一些知识，这两天在家，把这个sql xml的一些知识给学习了一下，下边是一些学习过程中的总结，希望能给大家带来一些帮助。测试用的数据?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518use TestDBgocreate Table TbFroXML(id uniqueidentifier primary key,name nvarchar(50),type nvarchar(50)goinsert 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(),笨笨)go1. For Xml Path-无名称列，针对行集合中的每一行生成一个对应的row元素，格式：1select 1 for xml path-生产格式 XXXselect name from TbFroXML for xml path-去掉name标签，只剩下row标签，格式：BBenBBOlive.select name+ from TbFroXML for xml path-只生成要查询的列的数据，去掉row元素，格式：BB未来明天今天BBenOliveselect name+ from TbFroXML for xml path()-生成格式：BBenselect id as id,name from TbFroXML for xml path-以NMG标记作为行标记，格式：BBen未来select name from TbFroXML for xml path(NMG)-以NMG标记作为行标记，以Mg标记作为内标记，格式：BB未来select name as MG from TbFroXML for xml path(NMG)-生成格式：BBselect name as BBen/Olive from TbFroXML for xml path-生成格式：BB未来select id as id,name as * from TbFroXML for xml path-被指定为列名的路径为data()，则在生成的XML 中，该值将被作为一个原子值来处理,-生成格式：BBMGselect id as id,name as name,name,TYPE as data() from TbFroXML for xml path-默认情况下，列中的Null 值映射为“缺少相应的属性、节点或元素，使用ELEMENTS 指令请求以元素为中心的XML 并指定XSINIL 来请求为NULL 值添加元素，格式： 笨笨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)2. For Xml Row/Auto模式-auto模式，格式：select * from TbFroXML for xml auto-elements选项,将每列值都映射为row元素的子元素，格式：41D6A175-C079-4861-9C75-2EE48A62C3BCBBMGselect * from TbFroXML for xml auto,elements-row 模式：格式：7B890E0B-C470-4E93-89A4-3041B70E8DF3笨笨select * from TbFroXML for xml raw,elements-elements选项，值为XSINIL 时，将结果集中的值为null 的列映射为xsi：nil=true属性的元素,格式：41D6A175-C079-4861-9C75-2EE48A62C3BCBBMGselect * from TbFroXML for xml raw, elements xsinil-xmldata:结果请求架构select * from TbFroXML for xml raw,xmldata-xmlschema：结果请求架构select * from TbFroXML for xml raw,xmlschema3. For XML Explicitselect 1 as Tag,NULL as Parent,3 as node!1 -node!1代表的是根节点，node为节点名，代表节点层次结构的节点级别for xml explicitselect 1 as Tag,null as Parent,Null as root!1,null as node!2!id!element-node!2!id!ELEMENTnode 代表的是节点名称;2代表的节点层次;id 表示的是元素名称;.ELEMENT 选项表示向 元素添加了 元素子级，而不是添加属性。union allselect 2 as Tag,1 as Parent,null,idfrom TbFroXML where id!=null for xml explicit4. XPathXPath 使用路径表达式来选取 XML 文档中的节点或节点集。节点是通过沿着路径 (path) 或者步 (steps) 来选取的。4.1 选取节点下面列出了最有用的路径表达式：表达式描述nodename选取此节点的所有子节点。/从根节点选取。/从匹配选择的当前节点选择文档中的节点，而不考虑它们的位置。.选取当前节点。.选取当前节点的父节点。选取属性。4.2 谓语（Predicates）谓语用来查找某个特定的节点或者包含某个指定的值的节点。谓语被嵌在方括号中。例： /root/Info/user14.3 选取未知节点XPath 通配符可用来选取未知的 XML 元素。通配符描述*匹配任何元素节点。*匹配任何属性节点。node()匹配任何类型的节点。例：/root/info/* info节点下的所有元素节点/root/info/* info 节点下所有的属性节点/user* 所有带有属性的user节点4.4 选取若干路径通过在路径表达式中使用“|”运算符，您可以选取若干个路径。例：/User | /root/Info/id5. XQuery5.1 基本的查询DECLARE dataSource XML SET dataSource= 笨笨MGOliveMG墨遥NMG-获取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)-获取所有uid1的所有Info节点select dataSource.query(/root/info./useruid1)if then else 表达式select dataSource.query(if (1=3) then /root/info./useruid=1 else /root/info./username=Olive)-路径表达式步骤中的谓词,获取第一个info节点下的user节点下的nameselect dataSource.query(/root/info1/user/name)-聚合函数select dataSource.query(count(/root/info./usertype=MG)-FLWOR语法：For、Let、Where、Order by、Returnselect dataSource.query(for $li in /root/info/user/name1 return string($li)select dataSource.query(for $li in /root/info,$lii in $li/user/name1 return string($lii)select dataSource.query(for $li in /root/info/user order by $li/uid descending return $li)select dataSource.query(for $li in /root/info/user order by local-name($li) return $li)5.2 Value()-value()获取某一节点或其属性的值，然后将其赋值给nvarchar类型select dataSource.value(/root/info/user1/name)1,nvarchar(20)5.3 Exist()select dataSource.exist(/root/info/user/nametext()1=笨笨)-将日期类型的节点属性转换为日期类型再与对应的值进行比较declare date xmlset date=-exist()select date.exist(/root(date cast as xs:date?) eq xs:date(2013-10-07)-将日期类型的节点的值转换为日期类型再与对应的值进行比较，datetext()1 cast xs:date?，将节点值转换为日期类型declare date1 xmlset date1=2013-10-07select date1.exist(/root/date(text()1 cast as xs:date?) eq xs:date(2013-10-07)-取任意属性的值：sql:variable(attriname)declare attr nvarchar(20)=uidif dataSource.exist(/root/info/user/*local-name()=sql:variable(attr)=1select OKelse select dataSource.query(/root/info/user/namelocal-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 typefrom dataSource.nodes(/root/info/user) T(c)-第一个value方法获取b节点下的值:cec,第二个value方法获取b节点下的值：c declare xml xml=cecselect xml.value(/root/a/b)1,nvarchar(20),xml.value(/root/a/b/text()1,nvarchar(20)-组合使用declare xml xml =declare pos int=2select 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,BenbenMGselect id,data=data.query(sql:column(id)/root/info/name/root/info/type) from tb-contains：模糊查询contains(.,XX)select t.c.query(.) from dataSource.nodes(/root/info/user./namecontains(.,笨) t(c)5.5 Modify()5.5.1 Insert-在某一节点下添加一个子节点insert into，as first/as last 指定节点插入的位置set dataSource.modify(insert F as last into (/root/info/user