ms sql server读取xml文件存储过程-sp_xml_preparedocument
最近要在存储过程中读取xml中节点的值,然后进行sql操作;
要使用到的系统存储过程如下:sp_xml_preparedocument
create procedure [dbo].[pro_Test_Readxml] @sData ntext as --XML 定义变量 并 解析赋值 declare @xmldata_id int exec sp_xml_preparedocument @xmldata_id output,@sData declare @sPage int --页码 select @sPage=page from openxml(@xmldata_id,'/DataTable/diffgr/NewDataSet/PARAMS',2)with (page int) declare @sRp int --每页记录数 select @sRp =rp from openxml(@xmldata_id,'/DataTable/diffgr/NewDataSet/PARAMS',2) with (rp int) declare @sZjlx varchar(10)--证件类型 select @sZjlx =zjlb from openxml(@xmldata_id,'/DataTable/diffgr/NewDataSet/PARAMS',2) with (zjlb varchar(10)) declare @sZjhm varchar(50) --证件号码 select @sZjhm =zjhm from openxml(@xmldata_id,'/DataTable/diffgr/NewDataSet/PARAMS',2) with (zjhm varchar(50)) declare @sFlag varchar(50) --标志 select @sFlag =flag from openxml(@xmldata_id,'/DataTable/diffgr/NewDataSet/PARAMS',2) with (flag varchar(50)) exec sp_xml_removedocument @xmldata_id if @@ERROR <> 0 begin select '解析xml数据失败!' return end declare @iRecBegin varchar(6),@iRecEnd varchar(6),@sSql varchar(1000),@sSqlText varchar(3000) if(@sRp <>'' and @sPage <> '') begin select @iRecBegin=@sRp * (@sPage -1)+1,@iRecEnd=@sRp * @sPage end else begin select @iRecBegin =1,@iRecEnd=20 end select @sSql = '',@sSqlText ='' if ISNULL(@sZjlx,'')<>'' begin if @sZjlx ='99' select @sSql =@sSql+''--sql end if ISNULL(@sFlag,'')<>'' begin begin select @sSqlText =''+@sSql;---sql end --print @sSqlText EXEC(@sSqlText) GO
执行如下:
EXEC pro_Test_Readxml '<DataTable><diffgr><NewDataSet><PARAMS><page>1</page><rp>10</rp><zjlb>99</zjlb><zjhm>31011600101000120141211155030551</zjhm><falg>1</flag></PARAMS></NewDataSet></diffgr></DataTable>'
一个完整的例子:
create procedure [dbo].[usp_Jb_Zlzfx_Gwsc] @sData ntext as set nocount on declare @idoc int exec sp_xml_preparedocument @idoc output ,@sData select * into #TMP_PARAS from OpenXML(@idoc,'PARAS/ITEM',2) with( XM varchar(50), ZZSCFS varchar(30), DCYSXM varchar(50), DCRQ varchar(10), INFO varchar(1000) ) exec sp_xml_removedocument @idoc if @@ERROR <> 0 begin select 'F' as RST ,'1.解析XML数据失败!' as MSG return end select * FROM #TMP_PARAS
执行:
exec usp_Jb_Zlzfx_Gwsc '<PARAS><ITEM><XM>张三</XM><ZZSCFS>1</ZZSCFS><DCYSXM>魏飞</DCYSXM><DCRQ>2016-12-06</DCRQ><INFO>text</INFO></ITEM></PARAS>'
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨小小打赏一下吧,如果囊中羞涩,不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.
![qq群](https://files-cdn.cnblogs.com/files/hoaprox/qqqun.bmp)
![微信](https://files.cnblogs.com/files/hoaprox/zsm.bmp)