Using NULL Xml Parameter

Hi all,

I'm passing a list of values to a stored procedure using an XML parameter - using SQL 2005.

The stored procedure is used to search a table for matching values so this list can contain one or many values, or could be NULL to indicate all values should be returned.

Returning one or more than one value works fine in the code sample given below. How can I phrase my query to cope with the scenario of the parameter being NULL to select all values?

I currently have the following code to cope with one or many values being passed:






Code Snippet

SELECT * FROM dbo.MyTable
WHERE idField IN
(SELECT paramvalues.id.value('.', 'int') FROM @testxml.nodes('/rootnode/idfieldvalue') as paramvalues(id))
where @testxml is the XML parameter passed into the stored procedure.

Thanks as always

Shivonne