摘要:SQL 2005: 把XML格式字串轉換成表格
不知道大家都怎麼批量更新DB裡面的資料？我是利用送出XML字串給Stored Procedures的方式來更新資料，到了DB之後再轉換成表格，轉成表格就方便更新了。
送出字串如下：
1 <Data>
2 <row userId="67" userName="Kenny1" password="1234" />
3 <row userId="80" userName="Kenny2" password="5678" />
4 .....
5 </Data>
2 <row userId="67" userName="Kenny1" password="1234" />
3 <row userId="80" userName="Kenny2" password="5678" />
4 .....
5 </Data>
然後利用以下方式轉換成表格
01 DECLARE @tempTable TABLE (
02 userName NVARCHAR(20),
03 password NVARCHAR(20)
04 )
05
06 INSERT INTO @tempTable
07 SELECT Tbl.Col.value('@userId', 'INT'),
08 Tbl.Col.value('@userName', 'NVARCHAR(20)'),
09 Tbl.Col.value('@password', 'NVARCHAR(20)')
10 FROM @xml.nodes('//Data/row') Tbl(Col)
11
02 userName NVARCHAR(20),
03 password NVARCHAR(20)
04 )
05
06 INSERT INTO @tempTable
07 SELECT Tbl.Col.value('@userId', 'INT'),
08 Tbl.Col.value('@userName', 'NVARCHAR(20)'),
09 Tbl.Col.value('@password', 'NVARCHAR(20)')
10 FROM @xml.nodes('//Data/row') Tbl(Col)
11
你就會得到像這樣的表格
| 67 | Kenny1 | 1234 |
| 80 | Kenny2 | 5678 |
| ... | ... | ... |
有了表格，要做任何事情都很方便了。
還可以有更進階的應用喔！在declare @tempTable的時候如果多定義一欄如下：
1 DECLARE @tempTable TABLE (
2 RowId int identity, //多了這行
3 userId INT,
4 userName NVARCHAR(20),
5 password NVARCHAR(20)
6 )
2 RowId int identity, //多了這行
3 userId INT,
4 userName NVARCHAR(20),
5 password NVARCHAR(20)
6 )
有了流水號之後，你可以對每一row做更多更進階的應用了喔！(例如利用遞迴對每一row做篩選，符合條件的才進行下一步...等等)