Link to home
Create AccountLog in
Avatar of Molior
MoliorFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Error with XML document insert SQL 2005

I am trying to create a stored procedure in SQL 2005 to insert a XML document in to a table - when saving I receive the following error  " Inncorrect syntex near '(' "

Can anyone see anything wrong with the following?

DECLARE @HANDLEXML INTEGER

EXEC SP_XML_PREPAREDOCUMENT @HANDLEXML OUTPUT, @XMLTEXT

INSERT INTO back_issues
      (
      back_issue_id, Title_code, Variant_code, Price, Stock, Issue_year, Issue_month, Issue_day
      )
SELECT  Back_Issue_Id, Title_code, Variant_Code, Price, Stock, Issue_year, Issue_month, Issue_day
FROM OPENXML (@HANDLEXML, '/NewDataSet/Table', 2) WITH
      (
      Back_Issue_Id INT,Title_code VARCHAR(255),Variant_Code VARCHAR(255),Price MONEY,Stock VARCHAR(50),Issue_year VARCHAR(50),Issue_month VARCHAR(255),Issue_day VARCHAR(50)
      )

EXEC SP_XML_REMOVEDOCUMENT @HANDLEXML
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Please post the entire stored procedure as it stands there is nothing intrinsically wrong with that code.
Avatar of Molior

ASKER

CREATE PROCEDURE spBackIssues_BulkInsert
      (
            @XMLTEXT NTEXT,
            @BackupFile VARCHAR(255)
      )
AS

SET XACT_ABORT ON
BEGIN TRANSACTION

-- Backup table data to text file
EXECUTE spExportTable_ToFile
'SELECT *  FROM ocs_uk.dbo.back_issues ORDER BY back_issue_id',
@BackupFile

-- Bulk insert of new data from XML
DECLARE @HANDLEXML INTEGER

EXEC SP_XML_PREPAREDOCUMENT @HANDLEXML OUTPUT, @XMLTEXT

INSERT INTO back_issues
      (
            back_issue_id, Title_code, Variant_code, Price, Stock, Issue_year, Issue_month, Issue_day
      )
SELECT  Back_Issue_Id, Title_code, Variant_Code, Price, Stock, Issue_year, Issue_month, Issue_day
FROM OPENXML (@HANDLEXML, '/NewDataSet/Table', 2) WITH
      (
            Back_Issue_Id INT,Title_code VARCHAR(255),Variant_Code VARCHAR(255),Price MONEY,Stock VARCHAR(50),Issue_year VARCHAR(50),Issue_month VARCHAR(255),Issue_day VARCHAR(50)
      )

EXEC SP_XML_REMOVEDOCUMENT @HANDLEXML

COMMIT TRANSACTION
Avatar of Molior

ASKER

The SQL error I receive is:

Msg 102, Level 15, State 1, Procedure spBackIssues_BulkInsert, Line 32
Incorrect syntax near '('.

when trying to save the above from both Visual Studio 2005 and if trying directly in SQL 2005.

Many thanks.
I still do not see anything that would cause that type of error.  After I commented out the line:
EXECUTE spExportTable_ToFile
'SELECT *  FROM ocs_uk.dbo.back_issues ORDER BY back_issue_id',
@BackupFile

So that I would not get the following error:
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'spExportTable_ToFile'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

The stored Procedure compiled fine.  Can you post a typical Xml document so that I can fully test it out?
ASKER CERTIFIED SOLUTION
Avatar of Molior
Molior
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>although I dont understand why.<<
That is because SQL Server 7 did not support OPENXML() or any of the Xml functions available in SQL Server 2000.
>>Please leave any recommendations here.<<
I think my answer speaks for itself.
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
So pray tell, why even bother asking for recommendations if you are going to promptly ignore them?

Anthony
I take it that you think the words "I think my answer speaks for itself." constitute a 'recommendation'?

For starters, I have no way of knowing which comment you consider your 'answer' to be.

I work all day, every day moderating between Askers and Experts.
If you expect me to help you resolve a disagreement, you need to give me enough information to do so.

Vee_Mod
Community Support Moderator
Fair enough.
Good to go.
See you around the Zones.
V