Sql 解析XML 解决方案 xml解析错误 解决

1. 1、@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)')

Sql 解析XML 解决方案 xml解析错误 解决
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

  

爱华网本文地址 » http://www.413yy.cn/a/25101012/120910.html

更多阅读

Sql 解析XML 解决方案 xml解析错误 解决

1. 1、@XML 为数据传入的XML格式2. root 为根目录3. <A>为对应需要插入的表,详见一对多或者多对多的xml格式4. 多对多是<ID> 为该条数据对应的从表的唯一标识,可以为其他名称的字段。如:<Customer><ID>1</ID></ Customer ><CustomerBra

Apache支持ASP的解决方案 asp sql盲注解决方案

由于Apache服务器没有内建ASP的功能,因此我们需要自行找到适用的ASP模块,通过查找相关资料,获知目前有三个套件可提供此功能:1、Chili soft ASP:现已经被sun收购,并入了SunONE ASP中;能较完美支持microsoft ,ASP3.0VBScript/JScript 5.5, A

声明:《Sql 解析XML 解决方案 xml解析错误 解决》为网友无双谱分享!如侵犯到您的合法权益请联系我们删除