解讀SQLServer2005 XML在T－SQL中的應用_Mssql數據庫教程
      
編輯Tag賺U幣
      


      教程Tag：暫無Tag,歡迎添加,賺取U幣!
      


      

      推薦：解析Asp.net編程中的數組基礎實例學習
      Asp.net數組(Array)可以視為儲存多個數據類型相同的值的變量，以同一個變量名稱，不同的索引值來區分表示多個值。多用來存放性質或類型相同的數據。

1.數組的聲明

數組的聲明有以
      
    
        
      
            以下為引用的內容：
            
      *
      
            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 v
      
            FROM
      
            (SELECT CAST('<r>' REPLACE(@s,',','</r><r>') '</r>' AS XML) x) a
      
            CROSS 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)) b
      
            
      SELECT * 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 null
      
            drop 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 null
      
            drop 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'
      
            Go
      
            

      
            SELECT 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'
      
            Go
      
            
      declare @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 ' | |') a
      
            from
      
            (
      
            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
      
            ) x
      
            from ta
      
            ) base
      
            
      where x.value('
      
            if (sql:column("base.type")="1") then
      
            if(
      
            (/item/text())[1]<sql:variable("@s")
      
            and
      
            (/item/text())[2]>sql:variable("@s")
      
            )
      
            then 1
      
            else 0
      
            else
      
            count(//item[text()>sql:variable("@s")])
      
            '
      
            ,
      
            'int'
      
            )>0
      
            go
      
                  		
        
      
          

      分享：談SQL Server 2005最后升級：SP3年底發布
      雖然SQL Server 2008已經發布了一段時間，但微軟并沒有忘記活了三年多的SQL Server 2005，最后一個升級服務包SP3也將在今年底如期推出。 

SQL Server持續性服務首席項目經理Nosheen
      


      

      


      來源：模板無憂//所屬分類：Mssql數據庫教程/更新時間：2009-09-17


      


      



      


      



      相關Mssql數據庫教程：