Hi,

I have a sproc that inserts an XML document into a table. Therefore, many rows are created at once. One of the fields of my table is called JobNumber.

How can I perform the insert such that JobNumber starts at 1 and is incremented for each inserted job upto n jobs.

I do not want an identity column since the the ID is a different field called JobSearchSet.

Thus one JobSearch might generate XML for 5 jobs therfore they will be numbered 1 to 5

A different Job search (and hence a different JobSearchSet) might generate XML for 140 jobs which I want numbered 1 to 140.

Any ideas.

Thanks

hi,

i suppose u r inserting using a loop. Create a local variable of datatype number and initialize it to 0
In your loop before inserting the record, add 1 to the local variable.
In this way at the first insert the local variable will be 1, second insert will be 2 etc.
If your procedure inserts more than 1 XML doc, be sure to reset the variable at the right time.

Hope this helps

Thanks,

No I dont use a loop. I use SQLPrepareDocument. I've thought about a trigger but the sproc might be called simultaneously by several users and I want to guarantee a consequtive number from 0 to n for each row as it relates to each XML doc being inserted. I think a trigger might get confused. I know that Oracle has a SEQUENCE function however, I can not find similar in SQL 2000. Only one XML document is being inserted and all the elements within it are dealt with transparently.

Here is the sproc. As you can see, I have declared a local variable @JobNumber. I've tried to select it and increment it in one statement (similar to what you would do if generating ID's from a table) however it will not accept the syntax.

Any ideas?


CREATE PROCEDURE dbo.Insert_tblSearchSetResults
@SearchSet varchar(1000),
@Mode varchar(1),
@XMLData text
AS
BEGIN
DECLARE @idoc int
DECLARE @doc varchar(1000)
declare @JobNumber tinyint

SET NOCOUNT ON

-- set the jobNumber to 1
SET @JobNumber = 1

-- remove first 77 characters from search set since they will always be the same
SET @searchSet = SUBSTRING(@searchset, 78, LEN(@searchSet)-77)

-- update cache stats
EXEC UpdateSearchSetCacheStats @searchSet, 1 -- 1 idicates creation of results

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLData

IF EXISTS (SELECT [ID] FROM sysobjects WHERE([Name] = 'tblSearchSetResults'))BEGIN

-- SELECT stmt using OPENXML rowset provider
INSERT INTO [ESAPI].[tblSearchSetResults]
SELECT @SearchSet AS [SearchSetID],
@Mode AS [SearchMode],
*,
@JobNumber,
GETDATE() AS [timeCached]
FROM OPENXML (@idoc, '/JobVerbose/Record',2)
WITH (
RecordsReturned int '../RecordsReturned',
MatchCount int '../MatchCount',
VacID varchar(30),
VacTitle varchar(130),
VacSocCode varchar(5),
VacWageText varchar(52),
VacHoursText varchar(60),
VacWage varchar(52),
VacWageQualifier varchar(2),
VacHours float,
VacCurrency varchar(3),
VacHoursQualifier varchar(9),
Location varchar(40),
VacPermTempFlag varchar(1),
DateReceived varchar(8),
ESVac varchar(1),
Quality int,
ErrorCode varchar(10) '../ErrorCode',
ErrorDescription varchar(250) '../ErrorDescription'
)
END ELSE BEGIN

-- SELECT stmt using OPENXML rowset provider
SELECT @SearchSet AS [SearchSetID],
@Mode AS [SearchMode],
*,
GETDATE() AS [timeCached]
INTO [ESAPI].[tblSearchSetResults]
FROM OPENXML (@idoc, '/JobVerbose/Record',2)
WITH (
RecordsReturned int '../RecordsReturned',
MatchCount int '../MatchCount',
VacID varchar(14),
VacTitle varchar(130),
VacSocCode varchar(5),
VacWageText varchar(52),
VacHoursText varchar(60),
VacWage varchar(52),
VacWageQualifier varchar(2),
VacHours float,
VacCurrency varchar(3),
VacHoursQualifier varchar(9),
Location varchar(40),
VacPermTempFlag varchar(1),
DateReceived varchar(8),
ESVac varchar(1),
Quality int,
ErrorCode varchar(10) '../ErrorCode',
ErrorDescription varchar(250) '../ErrorDescription'
)
END

EXEC sp_xml_removedocument @iDoc

SET NOCOUNT OFF
END
GO

hi,

because this looks like MSSQL server, i cannot make any comments on ur code. In oracle is works the way i described by just adding a counter and incrementing at at the right time, or using a sequence.

Hope this helps.

Thanks again.

I have implemented a solution. Basically, I use a local table variable with an identity column. Because it's local to the sproc it will generate a consequtive sequence and will not be interfered with by other users calling the sproc. At the end I then insert * from the local table into the main table.

This is not ideal since there are now two inserts and there will be a possibility of locking issues when several instances of the sproc are trying to perform the final insert into the main table.

Will have to wait and see.

Regards

the_little_hairy_welshman