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>'