以下是存储过程里的语句：
……
set @xml = SUBSTRING(@goodsListXml,39,LEN(@goodsListXml)-38);
--批量插入
INSERT INTO Goods(GOODS_ID, GOODS_NAME, GOODS_NUMBER, GOODS_CODE, BAR_CODE, STOCK, ISLOW_GOODS,
CATEGORY_ID, CATEGORY_NAME, BOX_NUMBER, LICENSE_NUMBER, STANDARD_CODE, FIRM_NAME, FIRM_ADDRESS, FIRM_TELEPHONE,
FIRM_CELLPHONE, QUALITY_PERIOD, GOODS_UNIT_ID, GOODS_UNIT_NAME, NORMS, WEIGHT, LOCATION, USER_ID, USER_NAME,
CREATE_TIME, CREATE_DATE, REMARK, ISACTIVE, IS_AUTO_ALLOT,GROSS_WEIGHT
)
SELECT c.doc.value('GOODS_ID[1]','varchar(32)'),
c.doc.value('GOODS_NAME[1]','varchar(120)'),
c.doc.value('GOODS_NUMBER[1]','varchar(50)'),
c.doc.value('GOODS_CODE[1]','varchar(50)'),
c.doc.value('BAR_CODE[1]','varchar(20)'),
c.doc.value('STOCK[1]','int'),
c.doc.value('ISLOW_GOODS[1]','bit'),
c.doc.value('CATEGORY_ID[1]','varchar(32)'),
c.doc.value('CATEGORY_NAME[1]','varchar(50)'),
c.doc.value('BOX_NUMBER[1]','int'),
c.doc.value('LICENSE_NUMBER[1]','varchar(50)'),
c.doc.value('STANDARD_CODE[1]','varchar(50)'),
c.doc.value('FIRM_NAME[1]','varchar(200)'),
c.doc.value('FIRM_ADDRESS[1]','varchar(500)'),
c.doc.value('FIRM_TELEPHONE[1]','varchar(12)'),
c.doc.value('FIRM_CELLPHONE[1]','varchar(11)'),
c.doc.value('QUALITY_PERIOD[1]','varchar(50)'),
c.doc.value('GOODS_UNIT_ID[1]','varchar(32)'),
c.doc.value('GOODS_UNIT_NAME[1]','varchar(10)'),
c.doc.value('NORMS[1]','varchar(10)'),
c.doc.value('WEIGHT[1]','varchar(20)'),
c.doc.value('LOCATION[1]','varchar(50)'),
c.doc.value('USER_ID[1]','varchar(32)'),
c.doc.value('USER_NAME[1]','varchar(50)'),
c.doc.value('CREATE_TIME[1]','varchar(30)'),
c.doc.value('CREATE_DATE[1]','varchar(8)'),
c.doc.value('REMARK[1]','varchar(1000)'),
c.doc.value('ISACTIVE[1]','bit'),
c.doc.value('IS_AUTO_ALLOT[1]','varchar(1)'),
c.doc.value('GROSS_WEIGHT[1]','varchar(20)')
FROM @xml.nodes('/nodes/node') c(doc);
……
------解决方案--------------------
你的xml代码是什么样，可以帖出来吗？我们插入xml时好象方法与你不一样
------解决方案--------------------
LZ代码的原理是逐笔插入,因此效率较低.
建议查@goodsListXml的数据来源是什么?应该也是来自其他表中的数据.
改为批量插入的写法,效率自然会提高的.