Does anyone see why the following SQL code just hangs before it opens the
cursor?
It hangs before
PRINT 'about to open cursor'
I'm guessing it might have something to do with the fact the cursor is
selecting data from XML. But not sure, maybe someone else can see whats
going on?
This is on MS SQL 2005 Ent.
Can anyone recommend a good list for SQL similiar to this one? Thanks in
advance.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_importXML]
@filePath VARCHAR( 255 )
, @userIdentity INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR( 4000 ), @importIdentity INT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
SELECT @importIdentity = MAX( importIdentity ) + 1
FROM tbl_importXML
SET @sql = '
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
INSERT INTO tbl_importXML ( userIdentity, myXML )
SELECT ' + CAST( @userIdentity AS VARCHAR( 10 ) ) + ' AS userIdentity
, x.*
FROM OPENROWSET ( BULK ''' + @filePath + ''', SINGLE_CLOB ) AS x
PRINT ''Importing XML file''
COMMIT TRANSACTION
'
PRINT 'Finished importing XML file'
EXEC (@sql)
COMMIT TRANSACTION
DECLARE @myXML XML
SELECT @myXML = myXML
FROM tbl_importXML
WHERE (importIdentity = @importIdentity)
PRINT 'Selected XML from import table'
DECLARE @itemIdentity BIGINT
, @url VARCHAR( 255 )
, @title VARCHAR( 80 )
, @description VARCHAR( 8000 )
, @quantity SMALLINT
, @summary VARCHAR( 100 )
, @category VARCHAR( 1000 )
, @price SMALLMONEY
, @classifiedIdentity INT
, @categoryIdentity INT
, @localityIdentity INT
, @imported BIT
DECLARE csr_import CURSOR FOR
SELECT myRow.myColumn.value( '@Id[1]', 'BIGINT' ) AS itemIdentity
, myRow.myColumn.value( 'Url[1]', 'VARCHAR(255)' ) AS url
, RTRIM( LTRIM( myRow.myColumn.value( 'Description[1]', 'VARCHAR(8000)' ) )
) AS description
, myRow.myColumn.value( 'Quantity[1]', 'SMALLINT' ) AS quantity
, RTRIM( LTRIM( myRow.myColumn.value( 'Caption[1]', 'VARCHAR(100)' ) )
) ASsummary
, myRow.myColumn.value( 'Category[1]', 'VARCHAR(1000)' ) AS category
, CONVERT( SMALLMONEY, REPLACE( myRow.myColumn.value(
'(Pricing/BasePrice)[1]', 'VARCHAR( 255 )' ), 'AU $', '' ) ) AS price
FROM @myXML.nodes('//Product') myRow( myColumn )
PRINT 'about to open cursor'
OPEN csr_import
PRINT 'opened cursor'
SELECT @localityIdentity = localityIdentity
FROM tbl_address
WHERE (userIdentity = @userIdentity)
IF (@localityIdentity IS NULL)
BEGIN
RETURN
END
FETCH NEXT
FROM csr_import
INTO @itemIdentity
, @url
, @description
, @quantity
, @summary
, @category
, @price
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT 'in cursor'
SELECT @categoryIdentity = categoryIdentity
FROM tbl_importCategoryMapping
WHERE (remoteCategory = @category)
IF ( EXISTS ( SELECT itemIdentity FROM tbl_importedXML WHERE
itemIdentity [EMAIL PROTECTED]
) )
BEGIN
SET @imported = 1
END
ELSE
BEGIN
SET @imported = 0
END
IF (NOT @categoryIdentity IS NULL AND @imported = 0)
BEGIN
PRINT 'importing: ' + @title
INSERT INTO tbl_classified (
categoryIdentity
, userIdentity
, quantity
, title
, summary
, description
, price
, localityIdentity
, statusIdentity
)
VALUES (
@categoryIdentity
, @userIdentity
, @quantity
, @title
, @summary
, @description
, @price
, @localityIdentity
, 4 -- 4 = awaiting scanning, ie negative keyword check
)
SET @classifiedIdentity = SCOPE_IDENTITY();
END
END TRY
BEGIN CATCH
SET @classifiedIdentity = 0
END CATCH
INSERT INTO tbl_importedXML (
importIdentity
, itemIdentity
, classifiedIdentity
)
VALUES (
@importIdentity
, @itemIdentity
, @classifiedIdentity -- 0 means the classified is not imported
)
FETCH NEXT FROM csr_import
INTO @itemIdentity
, @url
, @description
, @quantity
, @summary
, @category
, @price
END
CLOSE csr_import;
DEALLOCATE csr_import
UPDATE tbl_importXML
SET completeDate = GETDATE()
WHERE (importIdentity = @importIdentity)
-- return the identity of the import
SELECT @importIdentity AS importIdentity
END
--
http://www.clickfind.com.au
The new Australian search engine for businesses, products and services
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
