先贴出xml结构
- XML code
<root><custom_id>-1</custom_id><country_id>24</country_id><channel_id>1</channel_id><currency_id>2</currency_id><weight1>1</weight1><weight2>1</weight2><basicweight>1</basicweight><price1>1</price1><price2>1</price2><minusweight>1</minusweight><isdelete>0</isdelete><iscubage>1</iscubage><cubage1>2</cubage1><cubage2>2</cubage2><createdate>2012/2/23 13:16:42</createdate><surcharge_list><surcharge><cost_id>0</cost_id><price>1</price><relation>0</relation><ordernum>1</ordernum></surcharge><surcharge><cost_id>0</cost_id><price>2</price><relation>0</relation><ordernum>2</ordernum></surcharge><surcharge><cost_id>0</cost_id><price>3</price><relation>0</relation><ordernum>3</ordernum></surcharge></surcharge_list></root>
这个xml中上面的都是在一个cost表中，下面surcharge_list的数据在surcharge表中。一个cost有多个surcharge
下面是有问题的sql代码
- SQL code
declare @idoc intdeclare @idos intdeclare @doc xmlset @doc='<root><surcharge_list><surcharge><cost_id>0</cost_id><price>1</price><relation>0</relation><ordernum>1</ordernum></surcharge><surcharge><cost_id>0</cost_id><price>2</price><relation>0</relation><ordernum>2</ordernum></surcharge><surcharge><cost_id>0</cost_id><price>3</price><relation>0</relation><ordernum>3</ordernum></surcharge></surcharge_list></root>'exec sp_xml_preparedocument @idoc output,@docselect * fromOPENXML(@idoc,'/root',2)with(custom_id int,country_id int,channel_id int,currency_id int ,weight1 decimal ,weight2 decimal,basicweight decimal,price1 decimal,price2 decimal,minusweight decimal,isdelete bit,iscubage bit,cubage1 decimal,cubage2 decimal ,createdate datetime)select @idos=@@IDENTITYinsert Surcharge(cost_id,price,relation,ordernum)select * fromOPENXML(@idoc,'/root/surcharge_list/surcharge',2)with(cost_id int,price decimal,relation int,ordernum int)
问题在于将cost的内容插入数据库以后返回的id要如何给下面的surcharge中的cost_id呢
使用modify等函数都只能修改xml的一个节点，当有多个surcharge时候就会报错了，希望大家给一个解决方案的意见，谢谢
------解决方案--------------------
- SQL code
exec sp_xml_removedocument @idoc
------解决方案--------------------
楼主此处为何需要使用modify()函数?