[SQL]SQL 2005: 把XML格式字串轉換成表格 2

2009-07-10

5462
0
SQL

摘要:[SQL 2005]把xml字串轉成table(資料表)

加強之前的這篇。

有的人用字串分隔技巧送出一個很長的字串，到了後端資料庫時再想辦法分割字串。我則是使用送出xml格式字串的方式，讓後端DB轉換成表格之後就可以做多種運用了。

這次的例子如下

後端的Stored Procedures寫成這樣：

ALTER PROCEDURE [dbo].[TestXmlSproc]
@data xml
AS
BEGIN

SET NOCOUNT ON;

Declare @table Table (
    rowID int identity,
    userName varchar(20),
    age int,
    level int
)

Insert Into @table
Select Tbl.Col.value('@userName', 'varchar(20)'),
    Tbl.Col.value('@age', 'int'),
    Tbl.Col.value('@level', 'int')
From @data.nodes('//Data/Item') Tbl(Col)

些下來你就可以自由運用@table這個暫存資料表了。下面是我利用迴圈做更進階的使用

ALTER PROCEDURE [dbo].[TestXmlSproc]
@data xml  --接收傳入的xml格式字串
AS
BEGIN

SET NOCOUNT ON;

Declare    @table Table (
  rowID int identity,
  userName varchar(20),
  age int,
  level int)
Declare    @intRowCounts int,
  @intRowIndex int,
  @intNewValue int,
  @intOriginalValue int

Set  @intRowIndex = 1

--把xml格式字串轉成資料表
Insert    Into @table
Select    Tbl.Col.value('@userName', 'varchar(20)'),
  Tbl.Col.value('@age', 'int'),
  Tbl.Col.value('@level', 'int')
From  @data.nodes('//Data/Item') Tbl(Col)

--取得total的row數量
Select    @intRowCounts = Count(RowId)
From  @table

While (@intRowIndex <= @intRowCounts)
Begin
    --取得新輸入的數值
    Select    @intNewValue = level
    From  @table
    Where    rowID = @intRowIndex

    --取得原始的數值
    Select    @intOriginalValue = level
    From  Member
    Where    userName Like (
        Select userName
        From     @table
        Where    rowID = @intRowIndex)

    --如果數值有變動則更新數值
    If (@intOriginalValue != @intNewValue)
    Begin
    Update    Member
    Set  level = @intNewValue
    Where    userName Like (
        Select    userName
        From  @table
        Where    rowID = @intRowIndex)
    End

    Set    @intNewValue = 0
    Set    @intOriginalValue = 0

    --記得更新index
    Set    @int_RowIndex = @int_RowIndex + 1
End

回首頁