Using XML Value method to pass values into a stored procedure


  • 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;


    Wednesday, June 13, 2012 4:35 AM


All replies