insert OPENXML result set into table

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 2005 Forums

Transact-SQL (2005)

insert OPENXML result set into table

Author

Topic

Disco Patrick
Starting Member

18 Posts

Posted - 2009-06-01 : 06:21:25

Hi,

So I've recently worked out how to use OPENXML and create a result set based on an XML document.

Next I want to insert these results into a table as new rows.

I can get this to work no problem when there is just one row in the result set.

However, when there is more than one row, it doesn't seem to insert anything. There's no error, the stored proc completes, but no new data in the table.

Probably something to do with the way I'm trying to do the insert, which is like this:



	INSERT INTO RTA
		(RTID
		,SLID
		,LLID
		,CAID)

	SELECT RTID, SLID, LLID, CAID

	FROM	OPENXML	(@intDoc, 'rTA')
			WITH	(RTID int '@rTID'
					,SLID int '@sLID'
					,LLID int '@lLID'
					,CAID int '@cAID')

So I'm assuming if the result set contains multiple rows, the INSERT statement doesn't know which value it should be inserting - of course, I want to insert all of them on different rows. How do I do this?

Disco Patrick
Starting Member

18 Posts

Posted - 2009-06-01 : 06:31:48

I've just run OPENXML on it's own and discovered that the result set actually contains no rows. Oops.

So what's wrong with this OPENXML statement?: