/* SQLServer2005 XML在T－SQL查詢中的典型應用整理:fcuandy 時間:2008.11.7前言:此文只講xml數據類型及相應的一些操作方法在解決日常T－SQL編程中的一些應用，而避開xml modify, xml schema,xml索引,命名空間等這些語法性或者生硬的一些問題(這些語法您可以查聯機叢書),即此文主要 講以xml的一些操作特性及xquery去解決編程問題.Tags:xquery ,FLWOR迭帶 ,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函數, xs:function等典型應用舉例: */--(1) --==================================================================== --拆分 DECLARE @s VARCHAR(100) SET @s='a,b,c,dd,ee,f,aa,a,aa,f'--常規做法(sql2000常用),以一split函數拆分串為表類型結構,如 --SELECT * FROM dbo.split(@s,',') a --當然，也可能是循環去拆分,或者以一輸助表的identity列利用charindex等函數拿identity列值與','的位置匹配實現拆分 --這些做法，roy_88及本人以前都整理過，不再累贅，可見推薦貼。即便 是xml法，也貼過多次，下面一筆帶過--XML做法: SELECT b.v FROM(SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a --將字串","換換為"</r><r>"并前后拼上<r>,</r>以用來構造xml串 CROSS APPLY(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b --使用 xml.nodes函數將xml串拆分為行 /* a b c dd ee f aa a aa f */--(2) --==================================================================== --去重,@s中出現的元素，重復的只要一個,希望結果為 'a,b,c,dd,ee,f' --常規做法，循環或函數,或臨時表拆后distinct --XML做法: --a.在(1)的基礎上進行;WITH fc AS --定義cte命名,將@s轉換為一個表結構 (SELECT DISTINCT b.v vFROM(SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) aCROSS APPLY(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b ) --對這個表利用xml方法進行行值拼接 SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')FROM(SELECT v=(SELECT ',' + v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b /* a,aa,b,c,dd,ee,f */--b FLWOR語句 + T-SQL組合: SELECT STUFF(v,1,1,'') FROM(SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a CROSS APPLY(SELECT x=(SELECT t.x.value('.','varchar(10)') v,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),TYPE)) b --利用row_number得到唯一idx CROSS APPLY(SELECT v=CAST(b.x.query('for $r in //r where count(//r[v=$r/v and idx<$r/idx])=0 return concat(",",xs:string($r/v[1]))') AS VARCHAR(MAX))) c --類似count計數法，取得v相同的節點集idx值最小的節點,原型為: --SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.id) /* a ,b ,c ,dd ,ee ,aa ,f */--c distinct-values SELECT REPLACE(v,' ',',') FROM(SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a CROSS APPLY(SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b --直接調用distinct-values函數來操作 /* a,b,c,dd,ee,f,aa */-- 導入去重, last() , position()DECLARE @doc xml SET @doc ='<?xml version="1.0" encoding="gb2312" ?> <employees><employee><empid>e0001</empid><name>蕭峰</name></employee><employee><empid>e0002</empid><name>段譽</name></employee><employee><empid>e0003</empid><name>王語嫣</name></employee><employee><empid>e0003</empid><name>張無忌</name></employee> </employees> ' create table people2 ( personid varchar(10) primary key ,name varchar(20) )INSERT people2 SELECT DISTINCT b.* FROM(SELECT x = @doc.query('for $e in //employee return //employee[empid = $e/empid][last()]')) a --FLWOR時,用當前節點去//emploee節點集中找節點集中empid等于當前節點的empid, 在找到的集合中取最后一個利用last()函數 CROSS APPLY(SELECT id=t.x.value('empid[1]','varchar(100)'),name=t.x.value('name[1]','varchar(100)') FROM a.x.nodes('//employee') AS t(x)) bSELECT * FROM people2 /* e0001 蕭峰 e0002 段譽 e0003 張無忌 */ GO drop table people2 GO --同組一選多，也可應用此方法，不過沒有必要，就不再累贅了。--(3) --==================================================================== --列名,列值相關 --a,按行聚合 declare @t table(Sname nvarchar(5), V1 float, V2 float, V3 float, V4 float, V5 float, V6 float) insert @t select N'張三', 0.11 , 0.21 , 0.29, 0.32 , 0.11, 0.08 insert @t select N'李四', 0.01 , 0.61 , 0.21, 0.73 , 0.21, 0.12 insert @t select N'張五', 0.31 , 0.21 , 0.23, 0.33 , 0.91, 0.65 insert @t select N'張六', 0.59 , 0.11, 0.26, 0.13, 0.01, 0.15 select b.* from(select x=cast((select * from @t for xml path('r')) as xml)) a cross apply(select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x) --r為二級節點(因為文檔本身無根節點，即為每項的頂級節點)即為一個r節點表示一條記錄. r下級節點，每個表示一個列，因為列名未知，所以用/*匹配所有節點，因為name為區別列，不參與聚合運算，故用local-name取得來過濾) b/* 張三 0.32 李四 0.73 張五 0.91 張六 0.59 */--b ,由值引到取列 if not object_id('T1') is nulldrop table T1 GO Create table T1([tId] int,[tName] nvarchar(4)) Insert T1 select 1,N'zhao' union all select 2,N'qian' union all select 3,N'sun' Go --> --> 借且(Roy)生成測試數據if not object_id('T2') is nulldrop table T2 Go Create table T2([tId] int,[zhao] nvarchar(1),[qian] nvarchar(1),[sun] nvarchar(1)) Insert T2 select 1,N'a',N'b',N'c' union all select 2,N'd',N'e',N'f' union all select 3,N'g',N'h',N'i' GoSELECT c.tid,c.tName,v FROM t1 c CROSS APPLY(SELECT x=(SELECT * FROM t2 WHERE tid=c.tid FOR XML PATH('r'),TYPE)) a CROSS APPLY(SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("c.tName")) ]/text()') FROM a.x.nodes('//r') AS t(x)) b/* 1 zhao a 2 qian e 3 sun i */--c, 列名，列值，與系統表 CREATE TABLE tb(f1 INT,f2 INT,x INT,z INT,d INT,ex INT,dd INT,vv INT) INSERT tb SELECT 1,2,3,5,11,3,2423,33 GO SELECT * FROM tb GO SELECT name,v FROM( SELECT name FROM sys.columns WHERE object_id=object_id('tb','u') ) a CROSS JOIN(SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) b CROSS APPLY(SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("a.name")) ]/text()') FROM b.x.nodes('//r') AS t(x) ) c /* f1 1 f2 2 x 3 z 5 d 11 ex 3 dd 2423 vv 33 */ GO DROP TABLE tb GO--(4) --一些綜合計算 --以下表 ta.a值 yyyymmdd-yyyymmdd表連續時間段，","表單個日期 If object_id('ta','u') is not null Drop table ta Go Create table ta(a varchar(100)) Go Insert into ta select '1 | |20080101-20080911' union all select '2 | |20080101，20080201，20080301，20080515，20080808' union all select '3 | |20080101，20080201，20080301，20080515，20081108' Godeclare @s varchar(8) select @s= convert(varchar(8),getdate(),112)select stuff(replace(replace(cast(x as varchar(1000)),'</item><item>',case when type='1' then '-' else '，' end),'</item>',''),1,6,type + ' | |') afrom(select left(a,1) type, cast('<item>' + replace(stuff(a,1,5,''),case when left(a,1)=1 then '-' else '，' end,'</item><item>')+ '</item>'AS XML) xfrom ta) basewhere x.value('if (sql:column("base.type")="1") thenif((/item/text())[1]<sql:variable("@s")and(/item/text())[2]>sql:variable("@s"))then 1else 0elsecount(//item[text()>sql:variable("@s")])','int')>0 goSQL Server中用XQuery分解XML數據 本文討論SQL Server 2005的新功能，它允許你將XML數據分解到關系格式中，而不必耗用太多內存。 在舉例說明如何分解上一篇文章中的數據時，我們首先了解一下XQuery和它在SQL Server 2005中為開發者提供的功能。 XQuery介紹 XQuery，也稱作XML Query，是一種查詢XML數據的語言，允許你提取所需的節點和元素。它由W3C定義，可用于今天的大多數主流數據庫引擎中，如Oracle、DB2和SQL Server。 SQL Server 2005 XQuery函數 下面的四個函數是SQL Server 2005中的XQuery函數。（注意，XML、XQuery語句和下面的函數都區分大小寫。例如，SQL編譯器接受XML數據中的.exist，但拒絕.EXIST或.Exist。） xml.exist 這個方法根據一個XML節點上的搜索表達式返回一個布爾值。例如，列表A中XML代碼片段中的語句將返回1（真）： SELECT @x.exist('/christmaslist/person[@gift = "socks"]') 這個語句返回0（假）： SELECT @x. exist ('/christmaslist/zach') 由于“Socks”一詞被封套，這個語句將返回0（假）。 SELECT @x.exist('/christmaslist/person[@gift = "socks"]')xml.value 這個方法接受一個XQuery語句并返回一個單獨值。使用列表A中同樣的XML代碼片段，不可以使用VALUE函數生成“betty”值，如下所示： SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)') 而XQuery生成“zach”值。 SELECT @x.value('/christmaslist[1]/person[2]/@name', 'VARCHAR(20)')xml.query 這個方法接受一個XQuery并返回一個XML數據類型的實例。可以按需要將這些查詢簡單或復雜化，下面是一個簡單的例子： SELECT @x.query('/christmaslist/person') 它返回XML文件： <person name="betty" gift="camera" /><person name="zach" gift="elmo doll" /><person name="brad" gift="socks" />xml.nodes 在你需要將一個XML數據類型變量中的數據分解到關系數據中時，這個方法十分有用。這個方法接受一個XQuery語句作為參數，并返回一個包含XML變量邏輯標量數據的行集。列表B中的查詢利用上面定義的XML變量，并將數據分解到一個結果集中，它顯示在XML變量中定義的人物姓名。 修改OPENXML存儲過程 現在我來說明如何修改上周的OPENXML存儲過程，使其可以應用XQuery功能。首先，我往XML變量中加載一些數據。如列表C所示。我們可以建立一個接受XML參數的過程，再應用XQuery函數把XML文件中的數據插入一個表中，而不必應用OPENXML。如列表D所示。 最初在數據庫中應用XML似乎有些難于處理，還要花一些時間習慣使用XQuery和Xpath查詢。但是，經過一段時間的學習以后，你就會發現在數據庫中應用XML數據相當實用。 例如，在上述存儲過程中應用XML數據，你只需調用一次數據庫，而不必像典型存儲過程編程那樣調用N次數據庫。這二者似乎區別不大，但對一個繁忙的系統而言，應用XML數據會有很大益處。而且，應用XQuery而非OPENXML還會顯著提高性能，對小型XML文件更是如此。 對我有用[0]丟個板磚[0]引用舉報管理TOP 精華推薦：[分享]查看索引信息/生成創建索引腳本fredrickhu (小F) 等　級： 2 5 更多勛章 #3樓 得分：0回復于：2010-02-02 09:51:09 SQL code select @data.query('//address[@data="+534654765876876"]')對我有用[0]丟個板磚[0]引用舉報管理TOP 精華推薦：牢記！SQL Server數據庫開發的二十一條軍規(SQL收藏)fredrickhu (小F) 等　級： 2 5 更多勛章 #4樓 得分：0回復于：2010-02-02 09:51:30 SQL code /* sql xml 入門:--by jinjazz--http://blog.csdn.net/jinjazz1、xml: 能認識元素、屬性和值2、xpath: 尋址語言，類似windows目錄的查找(沒用過dir命令的話就去面壁)語法格式，這些語法可以組合為條件："."表示自己，".."表示父親，"/"表示兒子，"//"表示后代，"name"表示按名字查找，"@name"表示按屬性查找"集合[條件]" 表示根據條件取集合的子集，條件可以是數 值：數字,last(),last()-數字 等布爾值：position()<數字,@name='條件',name='條件'條件是布爾值的時候可以合并計算：and or3、xquery: 基于xpath標的準查詢語言，sqlserver xquery包含如下函數exist(xpath條件)：返回布爾值表示節點是否存在query(xpath條件)：返回由符合條件的節點組成的新的xml文檔value(xpath條件,數據類型)：返回指定的標量值，xpath條件結果必須唯一nodes(xpath條件): 返回由符合條件的節點組成的一行一列的結果表 */declare @data xml set @data=' <bookstore> <book category="COOKING"><title lang="en">Everyday Italian</title><author>Giada De Laurentiis</author><year>2005</year><price>30.00</price> </book> <book category="CHILDREN"><title lang="jp">Harry Potter</title><author>J K. Rowling</author><year>2005</year><price>29.99</price> </book> <book category="WEB"><title lang="en">XQuery Kick Start</title><author>James McGovern</author><author>Per Bothner</author><author>Kurt Cagle</author><author>James Linn</author><author>Vaidyanathan Nagarajan</author><year>2003</year><price>49.99</price> </book> <book category="WEB"><title lang="cn">Learning XML</title><author>Erik T. Ray</author><year>2003</year><price>39.95</price> </book> </bookstore> '--測試語句，如果不理解語法請參考上面的xpath規則和xquery函數說明--1、文檔 select @data --2、任意級別是否存在price節點 select @data.exist('//price') --3、獲取所有book節點 select @data.query('//book') --4、獲取所有包含lang屬性的節點 select @data.query('//*[@lang]') --5、獲取第一個book節點 select @data.query('//book[1]') --6、獲取前兩個book節點 select @data.query('//book[position()<=2]') --7、獲取最后一個book節點 select @data.query('//book[last()]') --8、獲取price>35的所有book節點 select @data.query('//book[price>35]') --9、獲取category="WEB"的所有book節點 select @data.query('//book[@category="WEB"]') --10、獲取title的lang="en"的所有book節點 select @data.query('//book/title[@lang="en"]') --11、獲取title的lang="en"且 price>35的所有book節點 select @data.query('//book[./title[@lang="en"] or price>35 ]') --12、獲取title的lang="en"且 price>35的第一book的(第一個)title select @data.query('//book[./title[@lang="en"] and price>35 ]').value('(book/title)[1]','varchar(max)') --13、等價于12 select @data.value('(//book[./title[@lang="en"] and price>35 ]/title)[1]','varchar(max)') --14、獲取title的lang="en"且 price>35的第一book的(第一個)title的lang屬性 select @data.value('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]','varchar(max)') --15、獲取第一本書的title select Tab.Col.value('(book/title)[1]','varchar(max)') as titlefrom @data.nodes('bookstore')as Tab(Col) --16、獲取每本書的第一個author select Tab.Col.value('author[1]','varchar(max)') as titlefrom @data.nodes('//book')as Tab(Col) --17、獲取所有book的所有信息 selectT.C.value('title[1]','varchar(max)') as title,T.C.value('year[1]','int') as year,T.C.value('title[1]','varchar(max)')as title,T.C.value('price[1]','float') as price,T.C.value('author[1]','varchar(max)') as author1,T.C.value('author[2]','varchar(max)') as author2,T.C.value('author[3]','varchar(max)') as author3,T.C.value('author[4]','varchar(max)') as author4 from @data.nodes('//book') as T(C) --18、獲取不是日語(lang!="jp")且價格大于35的書的所有信息 selectT.C.value('title[1]','varchar(max)') as title,T.C.value('year[1]','int') as year,T.C.value('title[1]','varchar(max)')as title,T.C.value('price[1]','float') as price,T.C.value('author[1]','varchar(max)') as author1,T.C.value('author[2]','varchar(max)') as author2,T.C.value('author[3]','varchar(max)') as author3,T.C.value('author[4]','varchar(max)') as author4 from @data.nodes('//book[./title[@lang!="jp"] and price>35 ]') as T(C) http://www.tzwhx.com/newOperate/html/1/13/132/11692.html

轉載于:https://www.cnblogs.com/xiaofengfeng/archive/2010/09/16/1828469.html

總結

以上是生活随笔為你收集整理的SQL--XML的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。