SQL 解析XML 解决方案.docxVIP

Sql 解析XML 解决方案1、@XML 为数据传入的XML格式root 为根目录A为对应需要插入的表，详见一对多或者多对多的xml格式多对多是ID 为该条数据对应的从表的唯一标识，可以为其他名称的字段。如：CustomerID1/ID/ Customer CustomerBranchID1/ID/CustomerBranch注意案例存储过程只定义了2个参数，可以根据自己的具体需求增加参数。对单表（单条或者多条）的操作。declare @XML xmlset @XML=Nroot AID1/IDnametest1/name/A AID2/IDnametest2/name/A /root 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=Nroot CustomerCustomerName深圳大展/CustomerNameCustomerTypeID116/CustomerTypeID/Customer CustomerBranchBranchID2/BranchIDAreaID6/AreaID/CustomerBranchCustomerBranchBranchID2/BranchIDAreaID6/AreaID/CustomerBranch /root 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=Nroot CustomerID1/IDCustomerName深圳大展/CustomerNameCustomerTypeID116/CustomerTypeID/Customer CustomerID2/IDCustomerName艾默生/CustomerNameCustomerTypeID116/CustomerTypeID/CustomerCustomerBranchID1/IDBranchID2/BranchIDAreaID6/AreaID/CustomerBranchCustomerBranchID2/IDBranchID4/BranchIDAreaID6/AreaID/CustomerBranchCustomerBranchID2/IDBranchID2/BranchIDAreaID3/AreaID/CustomerBranch /root 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 Cust