Using xml parameter in Stored procedure example

This post contain examples of two different xml's which were passed from a asp.net 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>
<id>
<salestypeid>101</salestypeid>
<value>22</value>
</id>
<id>
<salestypeid>102</salestypeid>
<Salesvalue>50</Salesvalue>
</id>
<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
begin
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>
<productid>111</productid>
<productid>222</productid>
</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
begin
DECLARE @Product int
SET @Product = (Select ProductID from @Products where ProductRowID = @j)

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

0 comments:

Post a Comment

Disclaimer

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 sharepointprogrammingblogger@gmail.com .

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

@ Learning SharePoint.com