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
Not 100% sure that I understand what you're trying to do.... but assuming this is your XML file:
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>'
you could easily select all ObjectID values using the SQL Server 2005 XQuery features (much easier to use than the old openxml stuff):
SELECT
V.VOI.value('(.)[1]', 'int')
FROM
@Input.nodes('/Root/ApplicationEntity/Versions/Version/VersionObjectIdList/ObjectID') V(VOI)
or the list of the feature ID's :
SELECT
F.FID.value('(.)[1]', 'int')
FROM
@Input.nodes('/Root/ApplicationEntity/Versions/Version/VersionFeatureIdList/FeatureID') F(FID)
Now what do you want to do with those values??
Update: OK, so to insert the values, use this statement:
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)
and similarly for the features, too.