【Wonder原创】SQL 存储过程XML参数

代码

 1 CREATE PROCEDURE [dbo].[proc_Submit_LotNo_UnHold_Bat] 
 2     @xData        xml,    
 3     @holderY    varchar(20),    
 4     @notes        varchar(500),
 5     @loc        varchar(50)='%'
 6  AS
 7  begin
 8 
 9  ---- 省略代码
10 
11 declare list cursor for
12         select distinct T.C.value ('lotno[1]' , 'varchar(max)' ) as  lotno from @xData.nodes ('//LotList' ) as T(C)
13     open list 
14     fetch NEXT from list into @lotno
15     while ( @@FETCH_STATUS = 0)
16     begin
17                    --- 省略代码
18                 end
19 
20 ----省略代码
21 
22 
23 
24 end

@xData 数据来源：ds.GetXml()

代码

 1 DataSet ds = new DataSet();
 2             DataTable dt = new DataTable("LotList");
 3             dt.Columns.Add("lotno", typeof(string));
 4             ds.Tables.Add(dt);
 5 
 6             int lotLen = Convert.ToInt16(drpType.SelectedItem.Value.ToString());
 7             string lotno = "";
 8             string[] strArry = txtLotList.Text.Split('\n');
 9             for (int i = 0; i < strArry.Length; i++)
10             {
11                 lotno = strArry[i].Trim().ToUpper();
12                 if (lotLen == 8)
13                     lotno = lotno.Length > 8 ? lotno.Substring(0, 8) : lotno;
14                 if (lotno.Length > 0)
15                 {
16                     DataRow dr = dt.NewRow();
17                     dr["lotno"] = lotno;
18                     dt.Rows.Add(dr);
19                 }
20             }
21