Passing lists to SQL Server 2005 with XML Parameters

We frequently have requirement like

Passing IDs in comma saperated value to the sql server for further processing.

Generally what we do is just create loop and get the value from it and ...

But SQL 2005 comes up with XQuery. Using this you can easly pass such type of data also some complex thing too.

For example...

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>' 


SELECT ParamValues.ID.value('.','VARCHAR(20)')FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:

3
6
15

Read more..