Sql 解析XML 解決方案
1. 1、@XML 為數據傳入的XML格式
2. root 為根目錄
3. <A>為對應需要插入的表，詳見(jiàn)一對多或者多對多的xml格式
4. 多對多是<ID> 為該條數據對應的從表的唯一標識，可以為其他名稱(chēng)的字段。
如：<Customer><ID>1</ID></ Customer >
<CustomerBranch><ID>1</ID></CustomerBranch>
5. 注意案例存儲過(guò)程只定義了2個(gè)參數，可以根據自己的具體需求增加參數。
一、 對單表（單條或者多條）的操作。
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 ) AS RowNumber,
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 where RowNumber=@Count)
set @Count=@Count-1
end
四、 批量更新數據
declare @XML xml
set @XML=N'<root>
<Customer>
<CustomerID>1</CustomerID>
<CustomerName>大客戶(hù)_TestXML2</CustomerName>
</Customer>
<Customer>
<CustomerID>2</CustomerID>
<CustomerName>大客戶(hù)_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')
五、具體存儲過(guò)程案例（一對多或者多對多的根據案例代碼放入到對應的事務(wù)即可）
--測試存儲過(guò)程
ALTER PROCEDURE [dbo].[AddXML]
(
@xmlstr XML,
@ProcMessageCode int output
)
AS
BEGIN
set nocount on
set xact_abort on
--定義中間變量
declare @error int
--設置初始值
set @error = 0
--開(kāi)始事務(wù)處理
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
--回滾事務(wù)
rollback tran tranAddXML
--返回保存失敗
set @ProcMessageCode = '10020115'
end
else
begin
--提交事務(wù)
commit tran tranAddXML
--返回保存成功
set @ProcMessageCode = '10030114'
end
set xact_abort off
set nocount off
END
聯(lián)系客服
