SQL 解析XML 解决方案.docx

Sql 解析XML 解决方案1. 1、XML 为数据传入的XML格式2. root 为根目录3. 为对应需要插入的表，详见一对多或者多对多的xml格式4. 多对多是 为该条数据对应的从表的唯一标识，可以为其他名称的字段。如：1 15. 注意案例存储过程只定义了2个参数，可以根据自己的具体需求增加参数。一、 对单表（单条或者多条）的操作。declare XML xmlset XML=N 1test1 2test2 Insert into Temp(ID,Name) select S.value(ID)1,int) as ID,S.value(name)1,nvarchar) as name,from XML.nodes(/root/A) T(S)注意：A 为表名 ID，Name为此表对应的字段名二、 对多表（一对多）的操作declare CustomerID int declare XML xmlset XML=N 深圳大展116 2626 Insert into Customer(CustomerName,CustomerTypeID) select S.value(CustomerName)1,nvarchar) as CustomerName,S.value(CustomerTypeID)1,int) as CustomerTypeIDfrom XML.nodes(/root/Customer) T(S) set CustomerID =IDENTITY insert into CustomerBranch(CustomerID,AreaID,BranchID) select CustomerID , S.value(AreaID)1,int) as AreaID,S.value(BranchID)1,int) as BranchIDfrom XML.nodes(/root/CustomerBranch) T(S)三、 对多表（多对多）的操作declare CustomerID int declare Count intdeclare Error intdeclare XML xmlset XML=N 1深圳大展116 2艾默生116126246223 select Count =MAX(RowNumber)from ( select ROW_NUMBER() OVER (ORDER BY S.value(ID)1,int) ) AS RowNumber from XML.nodes(/root/Customer) T(S) as m while(Count0)begininsert into Customer(CustomerName,CustomerTypeID) select CustomerName,CustomerTypeIDfrom (select ROW_NUMBER() OVER (ORDER BY S.value(ID)1,int) desc ) AS RowNumber,S.value(CustomerName)1,nvarchar(200) as CustomerName,S.value(CustomerTypeID)1,int) as CustomerTypeIDfrom XML.nodes(/root/Customer) T(S) ) as m where RowNumber=Countset CustomerID=IDENTITY insert into CustomerBranch(CustomerID,AreaID,BranchID) select CustomerID , S.value(AreaID)1,int) as AreaID,S.value(BranchID)1,int) as BranchIDfrom XML.nodes(/root/CustomerBranch) T(S) where S.value(ID)1,int) =( select ID from ( select ROW_NUMBER() OVER (ORDER BY S.value(ID)1,int) desc ) AS RowNumber,S.value(ID)1,int) as ID from XML.nodes(/root/Customer) T(S) ) as m where RowNumber=Count)set Count=Count-1end四、 批量更新数据declare XML xmlset XML=N1大客户_TestXML22大客户_TestXML1 update Customer set CustomerName =m.value(CustomerName)1,nvarchar(200)from XML.nodes(/root/Customer) T(m)where CustomerID=m.value(CustomerID)1,int) 五、 具体存储过程案例（一对多或者多对多的根据案例代码放入到对应的事务即可）-测试存储过程ALTER PROCEDURE dbo.AddXML(xmlstr XML,ProcMessageCode int output )ASBEGINset nocount on set xact_abort on -定义中间变量 declare error int -设置初始值 set error = 0 -开始事务处理 begin tran tranAddXML insert into tt select S.value(ID)1,int) as ID, S.value(name)1,nvarchar(10) as name, S.value(age)1,int) as age from xmlstr.nodes(/root/tt) T(S) -记录错误 set error = error + ERROR if error 0 begin -回滚事务 rollback tran tranAddXML -返回保存失败 set ProcMessageCode =