sql server 中使用简单的xml作用却很大
不知道大家是否在为sql的版本犯过愁，我就犯过愁，之前我们维护的系统服务器为sql2005、sql2000，而我本地确是sql2008，但是需要导入一大批数据进系统，当时可愁死我了。不会要我直接拿excel去服务器导吧。人家也不允许呀。本地都已经测试好了，数据都已经导入到系统里面去，问题是我该怎么导入到系统中去呢？菜鸟的我终于找到办法了，我将我本地是数据弄成一串xml串，经过一点点加工。OK。搞定！
第一步：弄一串xml出来
select * from temptable for xml auto
第二步：将xml加一个根节点：<Flow></Flows>
第三步：将xml数据通过一下方法导入到你要导入的表中。这个脚本可不分sql版本哦。挺有用！
SET QUOTED_IDENTIFIER ON
DECLARE @count INT;
DECLARE @i INT;
SET @i=0;
DECLARE @next INT;
DECLARE @xml XML;
SET @xml='
<Flow>
<kf FlowID="03D41C31-AAD1-45B5-8732-DB187DAD0655" FlowName="管理策划控制程序" Description="" Content="<IMG id=imgMap style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" src="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg"> " jpgUrl="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg" CreateTime="2011-12-17T07:38:45.687" CreateUser="1621190393" LastModTime="2011-12-17T07:38:45.687" LastModUser="1621190393" FlowModule="813" aa="03D41C31-AAD1-45B5-8732-DB187DAD0655----管理策划控制程序" FlowLevelID="EAB79C11-51AA-4EAE-8012-EFB3486C0F3A" FlowDeptID="1118" SerialNum="026" ImgHeight="500" ImgWidth="500" />
<kf FlowID="03E35305-86F5-4599-83A4-7946E0D0EF01" FlowName="培训费用支付流程" Description="" Content="<IMG id=imgMap style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" src="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg"> " jpgUrl="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg" CreateTime="2011-12-17T07:38:45.687" CreateUser="1621190393" LastModTime="2011-12-17T07:38:45.687" LastModUser="1621190393" FlowModule="420" aa="03E35305-86F5-4599-83A4-7946E0D0EF01----培训费用支付流程" FlowLevelID="EAB79C11-51AA-4EAE-8012-EFB3486C0F3A" FlowDeptID="1119" SerialNum="019" ImgHeight="500" ImgWidth="500" />
<kf FlowID="04BE1EDC-3241-45BB-AE7D-8A69A9B1F9B3" FlowName="竞争性谈判子流程" Description="" Content="<IMG id=imgMap style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" src="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg"> " jpgUrl="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg" CreateTime="2011-12-17T07:38:45.687" CreateUser="1621190393" LastModTime="2011-12-17T07:38:45.687" LastModUser="1621190393" FlowModule="716" aa="04BE1EDC-3241-45BB-AE7D-8A69A9B1F9B3----竞争性谈判子流程" FlowLevelID="CD94D4CF-C15C-4ED9-8C01-D1F12549DD8D" FlowDeptID="1118" SerialNum="011" ImgHeight="500" ImgWidth="500" />
<kf FlowID="04C952EC-7B5B-451C-ADA3-A50063F388DE" FlowName="购机方案审批流程" Description="" Content="<IMG id=imgMap style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" src="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg"> " jpgUrl="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg" CreateTime="2011-12-17T07:38:45.687" CreateUser="1621190393" LastModTime="2011-12-17T07:38:45.687" LastModUser="1621190393" FlowModule="231" aa="04C952EC-7B5B-451C-ADA3-A50063F388DE----购机方案审批流程" FlowLevelID="CD94D4CF-C15C-4ED9-8C01-D1F12549DD8D" FlowDeptID="10305" SerialNum="031" ImgHeight="500" ImgWidth="500" />
</Flow>';
DECLARE @table table(FlowID NVARCHAR(50),FlowName NVARCHAR(200)
,[Description] NVARCHAR(200)
,CreateTime DATETIME ,CreateUser NVARCHAR(50)
,LastModTime DATETIME ,LastModUser NVARCHAR(50)
,FlowModule INT ,FlowLevelID NVARCHAR(50)
,FlowDeptID NVARCHAR(50),SerialNum NVARCHAR(10))
SELECT @count = COUNT('.') FROM @xml.nodes('/Flow') AS T(c);
IF @count>0
BEGIN
INSERT INTO KG_Flow (FlowID, FlowName, [Description],CreateTime,
CreateUser,LastModTime,LastModUser,FlowModule,FlowLevelID,FlowDeptID,SerialNum)
SELECT T.c.value('./@FlowID','NVARCHAR(50)') AS FlowID,T.c.value('./@FlowName','nvarchar(200)') AS FlowName,
T.c.value('./@Description','nvarchar(200)') AS [Description],
T.c.value('./@CreateTime','DATETIME') AS CreateTime,T.c.value('./@CreateUser','nvarchar(50)') AS CreateUser,
T.c.value('./@LastModTime','DATETIME') AS LastModTime,T.c.value('./@LastModUser','nvarchar(50)') AS LastModUser,
T.c.value('./@FlowModule','INT') AS FlowModule,T.c.value('./@FlowLevelID','nvarchar(50)') AS FlowLevelID,
T.c.value('./@FlowDeptID','nvarchar(50)') AS FlowDeptID,T.c.value('./@SerialNum','nvarchar(10)') AS SerialNum
FROM @xml.nodes('/Flow/kf') AS T(c);
END;
IF OBJECT_ID('dbo.textbook') IS NOT NULL
DROP TABLE dbo.textbook;
SELECT * into dbo.textbook FROM @table
SELECT * FROM dbo.textbook
UPDATE KG_Flow SET [Content] = '<IMG id=imgMap style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" src="http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg"> '
,jpgUrl = 'http://fserver./OEEPP/2011-12-16/707a19f6-fe67-4e30-9eb2-74e4625e2014new.jpg'
,ImgHeight = 500, ImgWidth = 500
