Using XML Value method to pass values into a stored procedure

Question

I'm trying to setup a series of tests that call stored procedures.  In order to do this, I would like to pull the test data from XML strings without creating individual SQL variables to store the values from XML just to pass it into the SP.  Here is an example:

DECLARE @testMessage XML;
SELECT @testMessage = '<TestData><Title>hi</Title></TestData>';

-- How can I retrieve values directly from XML to pass to a SP?
EXEC testParamFromXML @message=@testMessage.value('(/TestData/Title)[1]','VARCHAR(20)');

-- This works, but I would like to avoid creating extra variables
DECLARE @messageText VARCHAR(20);
SELECT @messageText = ISNULL(@testMessage.value('(/TestData/Title)[1]','VARCHAR(20)'),'Title')
EXEC testParamFromXML @message=@messageText;

---

Scott

Answer 1

Pass them as xml and use a table inside procedure to store them

Please refer to the below link.

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Reading XML into temp table. Then you can loop through and execute your query.

http://www.techrepublic.com/article/read-an-xml-file-from-sql-server/5796532

Cheer!

Answer 3

Hi,

I don't think you will be able to achieve what you are trying  to do (avoiding an extra variable), As some others pointed out you can change the way you process your XML or opt for your second option with a extra variable 

here is a similar discussion which could help understanding why this is not possible 

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/12236a2c-418d-46f6-ae2b-2a644e892f94

Regards
Satheesh