现在使用数据库来写存储过程,动不动参数就会用到xml ,当然罗,优势也很明显,参数相对固定,而且灵活,如果要修改或者什么的,中间接口层也不需要做变化,只需要修改封装的存储过程以及程序传参就ok了。
随着时间慢慢过,有时候就有一个存储过程,一个xml 来应对整个表的新增,修改,删除的情况了。而对于这个情况,我个人比较喜欢使用 Merge关键字来处理。但是如果表里面的列很多,那么复制黏贴啊之类的机械动作就会很多,而且没有什么价值。所以我就写了一个小脚本,应对了使用xml 来做表的增删改的作用首先我先创建一个表
CREATE TABLE employee( ID INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(50),age INT,birthdate DATE,salary MONEY )
然后是生成的脚本。通常解析xml 会有2种的解析方法,一种是直接用openxml 来进行解析,一种是使用 xml.nodes 的函数进行取值,这里我两种都可以进行一个简单处理生成
DECLARE @TableName VARCHAR(50) = 'employee',@XMLType TINYINT = 1,--1 使用with 格式,2 使用nodes 格式 @Path NVARCHAR(max) = 'root/employee',@HasAction BIT = 1 --0 没有动作 1 包含动作 DECLARE @Columns NVARCHAR(MAX),--通用列的串 @FilterColumns NVARCHAR(max),--过滤外键,主键的列 @On NVARCHAR(100),--自动生成主键去匹配 @sql NVARCHAR(MAX) SELECT @Columns = STUFF(( SELECT ',' + name FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) ORDER BY column_id FOR XML PATH('')),1,''),@FilterColumns = STUFF(( SELECT ',' + name FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND is_computed = 0 AND is_identity = 0 ORDER BY column_id FOR XML PATH('')),@On = STUFF(( SELECT 'AND TAR.' + c.name + ' = SOUR.' + c.name FROM sys.indexes a INNER JOIN sys.index_columns b ON a.object_id = b.object_id INNER JOIN sys.columns c ON c.object_id = b.object_id AND b.column_id = c.column_id WHERE a.object_id = OBJECT_ID(@TableName) AND a.is_primary_key = 1),4,'') SELECT @sql = ';WITH SOUR AS( SELECT '+ CASE WHEN @XMLType = 1 THEN REPLACE(@Columns,',CHAR(10) + REPLICATE(CHAR(9),2) + ',') WHEN @XMLType = 2 THEN STUFF((SELECT CHAR(10) + ',t.c.value(''(' + a.name + '/text())[1]'',''' + TYPE_NAME(user_type_id) + CASE WHEN a.system_type_id IN (167,175) THEN '(' + CASE WHEN a.max_length = -1 THEN 'max' ELSE RTRIM(a.max_length) END+ ')' WHEN a.system_type_id IN (231,239) THEN '(' + CASE WHEN a.max_length = -1 THEN 'max' ELSE RTRIM(a.max_length/2) END + ')' WHEN a.system_type_id IN (59,106,108) THEN '(' + RTRIM(a.max_length) + ',' + RTRIM(a.scale) + ')' ELSE '' END + ''') AS ' + a.name FROM sys.columns a WHERE object_id = OBJECT_ID(@TableName) ORDER BY column_id FOR XML PATH('')),2,'') ELSE '' END + CASE WHEN @XMLType = 1 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),[Action]' WHEN @XMLType = 2 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),t.c.value(''@Action'',''tinyint'') [Action]' ELSE '' END + ' FROM ' + CASE @XMLType WHEN 1 THEN ' OPENXML(@XmlInt,''' + @Path + ''',3) WITH(' + STUFF((SELECT CHAR(10)+ REPLICATE(CHAR(9),6) + ',' + a.name + ' ' + UPPER(b.name) + CASE WHEN a.system_type_id IN (167,175,231,239,108) THEN '(' + CASE WHEN a.max_length = -1 THEN 'MAX' ELSE RTRIM(a.max_length) END + ')' WHEN a.system_type_id IN (59,108) THEN '(' + RTRIM(a.precision) + ',' + RTRIM(a.scale)+ ')' ELSE '' END + ' ''' + a.name + '''' FROM sys.columns a INNER JOIN sys.systypes b ON a.system_type_id = b.xtype AND b.status = 0 WHERE object_id = OBJECT_ID(@TableName) AND a.is_computed = 0 ORDER BY column_id FOR XML PATH ('') ),8,'') + + CASE WHEN @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),[Action] tinyint ''@Action'')' ELSE ')' END WHEN 2 THEN ' @' + @TableName + '.nodes('''+@Path+''') as t(c)' ELSE '' END + '),TAR AS( SELECT ' + REPLACE(@Columns,') + ' FROM ' + @TableName + ') MERGE TAR USING SOUR ON '+@On+' WHEN NOT MATCHED ' + CASE WHEN @HasAction = 1 THEN ' AND SOUR.[Action] = 1 ' ELSE '' END + ' THEN INSERT(' + @FilterColumns + ')' + CHAR(10) + REPLICATE(CHAR(9),5) + ' VALUES (SOUR.' + REPLACE(@FilterColumns,SOUR.') + ') WHEN MATCHED ' + CASE WHEN @HasAction = 1 THEN ' AND SOUR.[Action] = 2 ' ELSE '' END + ' THEN UPDATE SET ' + STUFF(( SELECT ',' + CHAR(10) + REPLICATE(CHAR(9),5) + 'TAR.[' + name + ']= SOUR.[' + name + ']' FROM sys.columns a WHERE object_id = OBJECT_ID(@TableName) AND is_computed = 0 AND is_identity = 0 AND NOT EXISTS(SELECT * FROM sys.foreign_key_columns WHERE parent_object_id = a.object_id AND parent_column_id = a.column_id) ORDER BY column_id FOR XML PATH('') ),6,'') + ' '+ CASE WHEN @HasAction = 1 THEN ' WHEN MATCHED AND SOUR.[Action] = 3 ' ELSE 'WHEN MATCHED BY SOURCE ' END + ' THEN Delete;' PRINT @sql
(因为偷懒,所以使用的openxml 里面的那个 sp_xml_preparedocument 这里我是没有写的)(*^__^*) 嘻嘻……
;WITH SOUR AS( SELECT t.c.value('(ID/text())[1]','int') AS ID,t.c.value('(name/text())[1]','nvarchar(50)') AS name,t.c.value('(age/text())[1]','int') AS age,t.c.value('(birthdate/text())[1]','date') AS birthdate,t.c.value('(salary/text())[1]','money') AS salary,t.c.value('@Action','tinyint') [Action] FROM @employee.nodes('root/employee') as t(c)),TAR AS( SELECT ID,name,age,birthdate,salary FROM employee) MERGE TAR USING SOUR ON TAR.ID = SOUR.ID WHEN NOT MATCHED AND SOUR.[Action] = 1 THEN INSERT(name,salary) VALUES (SOUR.name,SOUR.age,SOUR.birthdate,SOUR.salary) WHEN MATCHED AND SOUR.[Action] = 2 THEN UPDATE SET TAR.[name]= SOUR.[name],TAR.[age]= SOUR.[age],TAR.[birthdate]= SOUR.[birthdate],TAR.[salary]= SOUR.[salary] WHEN MATCHED AND SOUR.[Action] = 3 THEN Delete; xml.nodes
下面这个是使用openxml来生成的
;WITH SOUR AS( SELECT ID,salary,[Action] FROM OPENXML(@XmlInt,'root/employee',3) WITH(ID INT 'ID',name NVARCHAR(100) 'name',age INT 'age',birthdate DATE 'birthdate',salary MONEY 'salary',[Action] tinyint '@Action')),TAR.[salary]= SOUR.[salary] WHEN MATCHED AND SOUR.[Action] = 3 THEN Delete; openxml
恩~然后就可放进去执行啦~~
这里只是一个很基本的用法。有几点要说明的
1、Xml的名称我默认和表名一致,有需要请改动
2、On的匹配模型我是使用主键来进行对应
其它如果有什么问题请告诉我补充~