Sql 解析XML 解决方案 xml解析错误 解决
2. root 为根目录
3. <A>为对应需要插入的表,详见一对多或者多对多的xml格式
4. 多对多是<ID> 为该条数据对应的从表的唯一标识,可以为其他名称的字段。
如:<Customer><ID>1</ID></ Customer >
<CustomerBranch><ID>1</ID></CustomerBranch>
5. 注意案例存储过程只定义了2个参数,可以根据自己的具体需求增加参数。
一、 对单表(单条或者多条)的操作。
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>大客户_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
更多阅读
安装OFFICE时显示错误1923的解决方案 asp 500错误解决方案
安装OFFICE时提示错误--------------------------------------------------------------------------------------------------------错误1923。无法安装服务,"OFffice SourceEngine"(ose)。请确认你有足够的权力。
Sql 解析XML 解决方案 xml解析错误 解决
1. 1、@XML 为数据传入的XML格式2. root 为根目录3. <A>为对应需要插入的表,详见一对多或者多对多的xml格式4. 多对多是<ID> 为该条数据对应的从表的唯一标识,可以为其他名称的字段。如:<Customer><ID>1</ID></ Customer ><CustomerBra
无法解析服务器的DNS地址?DNS解析错误怎么办? dns地址解析错误
1234567分步阅读经常有朋友电脑连不上网,出现无法解析服务器的DNS地址的问题!对于这个问题,先了解一下DNS,简单说就是将域名转换为IP地址功能的服务器。DNS解析不了,你输入的地址自然无法指向目标网络,当然也就无法上网了!工具/原料联网P
ClearCase一些错误解决方案 http 500错误解决方案
ClearCase一些错误解决方案(转)cleartool: Error: Not an object in a vob: "filename".This error occurs when you try to execute a cleartool command on something that is not inside a VOB. The file being worked on must r
Apache支持ASP的解决方案 asp sql盲注解决方案
由于Apache服务器没有内建ASP的功能,因此我们需要自行找到适用的ASP模块,通过查找相关资料,获知目前有三个套件可提供此功能:1、Chili soft ASP:现已经被sun收购,并入了SunONE ASP中;能较完美支持microsoft ,ASP3.0VBScript/JScript 5.5, A