Using xml parameter in Stored procedure example

This post contain examples of two different xml's which were passed from a web application to a Stored procdure for the purpose of adding them into a table.
You can see how i loop through the temp table to get each value of the xml.

Examle I

declare @SalesTypeIds xml
SET @SalesTypeIds = '<salestypes>

-- Get values from @SalesTypeIds xml list
DECLARE @SalesTemp TABLE (RowID int not null identity(1,1) primary key,SalesTypeID int, value varchar(20))

-- Adding values in SalesTemp table
INSERT INTO @SalesTemp (SalesTypeID, Salesvalue) SELECT ParamValues.ID.query('SalesTypeID').value('.','int') as SalesTypeID,ParamValues.ID.query(Salesvalue).value('.','VARCHAR(20)') as Salesvalue
FROM @SalesTypeIds.nodes('/SalesTypes/id') as ParamValues(ID)

-- Set Row Count for all values in SalesTemp table
Declare @i int
Select @i = min(RowID) from @SalesTemp
Declare @max int
Select @max = max(RowID) from @ SalesTemp

-- Loop through the SalesTemp values
while @i <= @max
DECLARE @SalesID varchar(100)
SET @SalesID = (Select SalesTypeID from @Sales where RowID = @i)
DECLARE @ valueofSales varchar(100)
SET @ valueofSales = (Select valueofSales from @Sales where RowID = @i)

-- Add your logic here to do something with the values

End -- loop ends

Example II

declare @ProductIds xml
SET @ProductIds ='<products>

-- Get values from @ProductIds xml list
DECLARE @Products TABLE (ProductRowID int not null identity(1,1) primary key,ProductID int)
INSERT INTO @Products (ProductID) SELECT ParamValues.ProductID.value('.','int')
FROM @productIds.nodes('/Products/ProductID') as ParamValues(ProductID)

-- Set Row Count for all Products in Products table
Declare @j int
Select @j = min(ProductRowID) from @Products
Declare @jmax int
Select @jmax = max(ProductRowID) from @Products

-- Loop through the @Products values
while @j <= @jmax
DECLARE @Product int
SET @Product = (Select ProductID from @Products where ProductRowID = @j)

-- Do something with the Product value
END -- loop ends


Post a Comment


This is a personal weblog. The opinions expressed here represent my own and not those of my employer or anyone else. Should you have any questions or concerns please e-mail me at .

Copyright (c) 2010 @ All rights are reserved.Do Not Copy.

@ Learning