Sql 解析XML 解决方案
- 1. 1、@XML 为数据传入的XML格式
- 2. root 为根目录
- 3. <A>为对应需要插入的表，详见一对多或者多对多的xml格式
- 4. 多对多是<ID> 为该条数据对应的从表的唯一标识，可以为其他名称的字段。
如：<Customer><ID>1</ID></ Customer >
<CustomerBranch><ID>1</ID></CustomerBranch>
- 5. 注意案例存储过程只定义了2个参数，可以根据自己的具体需求增加参数。
1.6 EXECUTE sp_xml_preparedocument 为sql自带的处理xml函数。
一、 对单表（单条或者多条）的操作。
declare @XML xml
set @XML=N'<root>
<A>
<ID>1</ID>
<name>test1</name>
</A>
<A>
<ID>2</ID>
<name>test2</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 xml
set @XML=N'<root>
<Customer>
<CustomerName>深圳大展</CustomerName>
<CustomerTypeID>116</CustomerTypeID>
</Customer>
<CustomerBranch>
<BranchID>2</BranchID>
<AreaID>6</AreaID>
</CustomerBranch>
<CustomerBranch>
<BranchID>2</BranchID>
<AreaID>6</AreaID>
</CustomerBranch>
</root>'
Insert into Customer(CustomerName,CustomerTypeID)
select S.value('(CustomerName)[1]','nvarchar') as CustomerName,
S.value('(CustomerTypeID)[1]','int') as CustomerTypeID
from @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 BranchID
from @XML.nodes('/root/CustomerBranch') T(S)
三、 对多表（多对多）的操作
declare @CustomerID int
declare @Count int
declare @Error int
declare @XML xml
set @XML=N'<root>
<Customer>
<ID>1</ID>
<CustomerName>深圳大展</CustomerName>
<CustomerTypeID>116</CustomerTypeID>
</Customer>
<Customer>
<ID>2</ID>
<CustomerName>艾默生</CustomerName>
<CustomerTypeID>116</CustomerTypeID>
</Customer>
<CustomerBranch>
<ID>1</ID>
<BranchID>2</BranchID>
<AreaID>6</AreaID>
</CustomerBranch>
<CustomerBranch>
<ID>2</ID>
<BranchID>4</BranchID>
<AreaID>6</AreaID>
</CustomerBranch>
<CustomerBranch>
<ID>2</ID>
<BranchID>2</BranchID>
<AreaID>3</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(@Count>0)
begin
insert into Customer(CustomerName,CustomerTypeID)
select CustomerName,CustomerTypeID
from (
select ROW_NUMBER() OVER (ORDER BY S.value('(ID)[1]','int') desc ) ASRowNumber,
S.value('(CustomerName)[1]','nvarchar(200)') as CustomerName,
S.value('(CustomerTypeID)[1]','int') as CustomerTypeID
from @XML.nodes('/root/Customer') T(S)
) as m where RowNumber=@Count
set @CustomerID=@@IDENTITY
insert into CustomerBranch(CustomerID,AreaID,BranchID)
select @CustomerID , S.value('(AreaID)[1]','int') as AreaID,
S.value('(BranchID)[1]','int') as BranchID
from @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 whereRowNumber=@Count)
set @Count=@Count-1
end
四、 批量更新数据
declare @XML xml
set @XML=N'<root>
<Customer>
<CustomerID>1</CustomerID>
<CustomerName>大客户_TestXML2</CustomerName>
</Customer>
<Customer>
<CustomerID>2</CustomerID>
<CustomerName>大客户_TestXML1</CustomerName>
</Customer>
</root>'
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
)
AS
BEGIN
set 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 = '10020115'
end
else
begin
--提交事务
commit tran tranAddXML
--返回保存成功
set @ProcMessageCode = '10030114'
end
set xact_abort off
set nocount off
END
