OpenXML in an Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OpenXML in an Insert

Author

Topic

JimmyFo
Starting Member

11 Posts

Posted - 2006-12-28 : 15:28:51

Hi folks, quick question on an insert. Part of the insert will be from parameters (that were put into other tables earlier in the procedure) and the other part will be from an XML document passed in. At least that's how I would like it. What is wrong with the code below? I need to insert multiple rows, but each row will have the same basic values (the first few regular inserts) then each row will have seperate date from each element in the XML document.

Insert into tblExpenseItem
		(ExpenseReportID, DateCreated, DateModified, CreatedBy, ModifiedBy, ExpenseTypeID, ItemName, ItemDescription, ItemData, ItemCost, ItemStatus)
		values
		(SCOPE_IDENTITY(), GETDATE(), GETDATE(), @IntEmployeeID, @IntEmployeeID,
		(Select * from OPENXML (@intDocHandle, '/Data/Expense',2)
		WITH (ExpenseType int, ItemName varchar(50), ItemDesc varchar(240),ItemData binary(50), ItemCost  decimal(8,2), ItemStatus varchar(3))))

The error is:

Msg 1046, Level 15, State 1, Procedure usp_SubmitExpenseToSupervisor, Line 46
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 319, Level 15, State 1, Procedure usp_SubmitExpenseToSupervisor, Line 47
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Thanks,
James

mandm
Posting Yak Master

120 Posts

Posted - 2006-12-28 : 15:52:10

This is the basic syntax you want when inserting multiple rows and inter-mixed XML and other data. I'm not sure how well the SCOPE_IDENTITY will work in this context so you may have to play with it.

Insert into tblExpenseItem
(
ExpenseReportID
, DateCreated
, DateModified
, CreatedBy
, ModifiedBy
, ExpenseTypeID
, ItemName
, ItemDescription
, ItemData
, ItemCost
, ItemStatus)
SELECT SCOPE_IDENTITY()
, GETDATE()
, GETDATE()
, @IntEmployeeID
, @IntEmployeeID
, myXML.ExpenseType
, myXML.ItemName
, myXML.ItemDesc
, myXML.ItemData
, myXML.ItemCost
, myXML.ItemStatus
FROM OPENXML (@intDocHandle, '/Data/Expense',2)
WITH (
ExpenseType int
, ItemName varchar(50)
, ItemDesc varchar(240)
, ItemData binary(50)
, ItemCost decimal(8,2)
, ItemStatus varchar(3)
) myXML

Mark

JimmyFo
Starting Member

11 Posts

Posted - 2006-12-28 : 16:08:15

Hey, that's looking like it works great! I'll do some more testing and let you know, thanks!

James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources