Wednesday, June 13, 2012 4:35 AM
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)','VARCHAR(20)'); -- This works, but I would like to avoid creating extra variables DECLARE @messageText VARCHAR(20); SELECT @messageText = ISNULL(@testMessage.value('(/TestData/Title)','VARCHAR(20)'),'Title') EXEC testParamFromXML @message=@messageText;
Wednesday, June 13, 2012 5:03 AM
Pass them as xml and use a table inside procedure to store them
Please refer to the below link.
ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
- Marked As Answer by Iric WenModerator Wednesday, June 20, 2012 7:57 AM
Wednesday, June 13, 2012 5:29 AM
Reading XML into temp table. Then you can loop through and execute your query.
Wednesday, June 13, 2012 6:40 AM
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