使用XML批量插入
本文关键字：插入 XML 使用 | 更新日期: 2023-09-27 18:06:23
我无法为我的表编写大容量插入的存储过程。
我想在[tbl_ReleaseHistory]中插入<ObjectID>的列表，保持<FeatureID>为空。
同样，当插入<FeatureID>到同一表时，<ObjectID>应该保持null。
我只能完成第一个<ObjectID>和<FeatureID>的插入，即2218和67从传递给虚拟执行的xml。
现在如何迭代<objectID>的插入列表?
那么还有其他方法吗?
如何使用XML执行批量插入?
CREATE PROCEDURE [dbo].[spVersionAddReleaseHistory] @ApplicationMaster NTEXT
AS
/****************************************************************************
DESCRIPTION:
------------
This script is used to insert new record to the table tbl_VersionMaster.
MAINTENANCE LOG:
DATE AUTHOR DESCRIPTION
---- ------ -----------
01/07/2011 Isha Initial Creation
/*DUMMY EXECUTION : */
DECLARE @return_value int
EXEC @return_value = [dbo].[spVersionAddReleaseHistory]
@ApplicationMaster = N'<Root><ApplicationEntity>
<Id>0</Id>
<versionId>0</versionId>
<Versions>
<Version>
<Application>1111</Application>
<Version>11.11.123.123</Version>
<VersionMajor>11</VersionMajor>
<VersionMinor>11</VersionMinor>
<VersionBuild>123</VersionBuild>
<VersionRevision>123</VersionRevision>
<VersionFeatureIdList>
<FeatureID>67</FeatureID>
<FeatureID>68</FeatureID>
<FeatureID>69</FeatureID>
</VersionFeatureIdList>
<VersionObjectIdList>
<ObjectID>2218</ObjectID>
<ObjectID>2219</ObjectID>
<ObjectID>2220</ObjectID>
<ObjectID>2221</ObjectID>
<ObjectID>2222</ObjectID>
<ObjectID>2223</ObjectID>
<ObjectID>2224</ObjectID>
<ObjectID>2225</ObjectID>
<ObjectID>2226</ObjectID>
<ObjectID>2227</ObjectID>
<ObjectID>2228</ObjectID>
<ObjectID>2229</ObjectID>
</VersionObjectIdList>
<Components>
<Component>2218-Cmpjhhghghjghjg</Component>
<Component>2219-NEW </Component>
<Component>2220-OLD</Component>
</Components>
<Tables>
<Table>2221-t</Table>
<Table>2223-ty</Table>
</Tables>
<StoredProcedures>
<StoredProcedure>2226-tr</StoredProcedure>
<StoredProcedure>2227-trigr</StoredProcedure>
</StoredProcedures>
<Triggers>
<Trigger>2226-tr</Trigger>
<Trigger>2227-trigr</Trigger>
</Triggers>
<Features>
<Feature>2224-ffu</Feature>
<Feature>2225-ffffu</Feature>
</Features>
</Version>
</Versions>
</ApplicationEntity></Root>'
SELECT 'Return Value' = @return_value
****************************************************************************/
DECLARE @hDoc INT
EXEC Sp_xml_preparedocument
@hDoc OUTPUT,
@ApplicationMaster
-- SET identity_insert tbl_versionmaster ON
DECLARE @mylastident AS int
SET @mylastident = (SELECT max(VM_VersionId) from [tbl_VersionMaster])
BEGIN
INSERT INTO [tbl_ReleaseHistory]
( [RL_VersionId] ,
[RL_ObjectId] ,
[RL_FeatureId]
)
SELECT
@mylastident ,
ObjectID ,
NULL
FROM OPENXML(@hDoc,'Root/ApplicationEntity/Versions/Version/VersionObjectIdList',2)
WITH(
ObjectID INT ,
FeatureID INT
) xmlitems
END
BEGIN
INSERT INTO [tbl_ReleaseHistory]
( [RL_VersionId] ,
[RL_ObjectId] ,
[RL_FeatureId]
)
SELECT
@mylastident ,
NULL ,
FeatureID
FROM OPENXML (@hDoc,'Root/ApplicationEntity/Versions/Version/VersionFeatureIdList',2)
WITH (
ObjectID INT ,
FeatureID INT
) xmlitems
END
SET NOCOUNT OFF;
EXEC Sp_xml_removedocument @hDoc
谢谢
不是100%确定我理解你想做什么....但假设这是您的XML文件:
DECLARE @Input XML
SET @Input = '<Root>
<ApplicationEntity>
<Id>0</Id>
<versionId>0</versionId>
<Versions>
<Version>
<Application>1111</Application>
<Version>11.11.123.123</Version>
<VersionMajor>11</VersionMajor>
<VersionMinor>11</VersionMinor>
<VersionBuild>123</VersionBuild>
<VersionRevision>123</VersionRevision>
<VersionFeatureIdList>
<FeatureID>67</FeatureID>
<FeatureID>68</FeatureID>
<FeatureID>69</FeatureID>
</VersionFeatureIdList>
<VersionObjectIdList>
<ObjectID>2218</ObjectID>
<ObjectID>2219</ObjectID>
<ObjectID>2220</ObjectID>
<ObjectID>2221</ObjectID>
<ObjectID>2222</ObjectID>
<ObjectID>2223</ObjectID>
<ObjectID>2224</ObjectID>
<ObjectID>2225</ObjectID>
<ObjectID>2226</ObjectID>
<ObjectID>2227</ObjectID>
<ObjectID>2228</ObjectID>
<ObjectID>2229</ObjectID>
</VersionObjectIdList>
.... (more stuff - not relevant here) ....
</Version>
</Versions>
</ApplicationEntity>
</Root>'
你可以很容易地选择所有的ObjectID值使用SQL Server 2005的XQuery功能(更容易使用比旧的openxml的东西):
SELECT
V.VOI.value('(.)[1]', 'int')
FROM
@Input.nodes('/Root/ApplicationEntity/Versions/Version/VersionObjectIdList/ObjectID') V(VOI)
或特性ID的列表:
SELECT
F.FID.value('(.)[1]', 'int')
FROM
@Input.nodes('/Root/ApplicationEntity/Versions/Version/VersionFeatureIdList/FeatureID') F(FID)
现在你想用这些值做什么??
Update: OK，那么要插入值，使用下面的语句:
INSERT INTO dbo.tbl_ReleaseHistory(RL_VersionId, RL_ObjectId, RL_FeatureId)
SELECT
V.VOI.value('(.)[1]', 'int'),
(some value for RL_ObjectId),
(some value for RL_FeatureId)
FROM
@Input.nodes('/Root/ApplicationEntity/Versions/Version/VersionObjectIdList/ObjectID') V(VOI)