使用 XML 进行批量插入
我无法编写存储过程来批量插入到我的表中。
我想将 [tbl_ReleaseHistory] 中，保持
此外，在将
我只能完成第一个
现在如何迭代
那么还有其他方法吗？
如何使用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
谢谢
I am unable to write a stored procedure for bulk insertion into my table.
I want to insert list of <ObjectID> into [tbl_ReleaseHistory] keeping <FeatureID> null.
Also while inserting <FeatureID> into the same table <ObjectID> should remain null.
I am able to accomplish only the insertion of first <ObjectID> and <FeatureID> i.e 2218 and 67 from the xml passed for dummy execution.
Now how to iterate list of <objectID> for insertion?
So Is there any other method?
How to perform Bulk insert using 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
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不能 100% 确定我理解您要执行的操作...但假设这是您的 XML 文件：
您可以使用 SQL Server 2005 XQuery 功能轻松选择所有
ObjectID值（比旧的 openxml 东西更容易使用）：或功能 ID 列表：
现在您想用这些值做什么？
更新： 好的，要插入值，请使用此语句：
对于功能也类似。
Not 100% sure that I understand what you're trying to do.... but assuming this is your XML file:
you could easily select all
ObjectIDvalues using the SQL Server 2005 XQuery features (much easier to use than the old openxml stuff):or the list of the feature ID's :
Now what do you want to do with those values??
Update: OK, so to insert the values, use this statement:
and similarly for the features, too.