XML parameter into table - 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 2005 Forums

Transact-SQL (2005)

XML parameter into table

Author

Topic

Angate
Starting Member

24 Posts

Posted - 2009-12-26 : 15:01:58

I am using SQL Server 2005 Express. I want to pass an xml parameter into a stored procedure and have it's contents inserted into a table. I also need to be able to insert a pair of fixed values, the ID of the parent row and the customer's ID, into the table along with each line from the XML. I have tried for over a week to figure this stuff out and have been unable to reverse engineer what I have found.

This so far is what I have (the first set of parameters is for the parent row):


ALTER PROCEDURE [dbo].[InsertMMChildCartItemtest]
(
	@customerNumber int,
	@productID int,
	@quantity smallint,
	@price decimal(6, 2),
	@dateUpdated smalldatetime,
	@children xml /****** <Products><product><id>11</id><quantity>2</quantity><date>10/10/10</date></product><product><id>12</id><quantity>3</quantity>date>10/10/10</date></product></Products> ******/
)
AS
	BEGIN
	SET NOCOUNT OFF;

	DECLARE @id int
	DECLARE @childTable TABLE (
		productID int,
		quantity smallint,
		price decimal(6, 2)
	)
	/******Insert the parent per the parameters and grab its new ID  ******/

	INSERT INTO [dbo].[cartTable] ([customerNumber], [productID], [quantity], [dateUpdated]) 
		 VALUES (@customerNumber, @productID, @quantity, @dateUpdated)
	 
	 SET @id = SCOPE_IDENTITY()
	END
	/******Insert the child rows, with some values comming from the parameters, some from the XML 

	INSERT INTO @childTable
		SELECT @children.query('/Products/product')
		
	INSERT INTO [dbo].[cartTable]
		SELECT @customerNumber, CT.productID, CT.quantity, CT.price, @id
		FROM @childTable as CT
	 ******/

Ideas?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2009-12-28 : 05:33:28

Try this script ,

INSERT INTO @childTable
SELECT ParamValues.ID.query('id').value('.','INT'),
ParamValues.ID.query('quantity').value('.','smallint'),
ParamValues.ID.query('date').value('.','datetime')
FROM @children.nodes('/Products/product') as ParamValues(ID)

and your script will work,

INSERT INTO [dbo].[cartTable]
SELECT @customerNumber, CT.productID, CT.quantity, CT.price, @id
FROM @childTable as CT

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Angate
Starting Member

24 Posts

Posted - 2009-12-28 : 17:19:39

I can't thank you enough, that worked beautifully. I think I see from what you did what it is that needs to be done in general for XML.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources