locked
Error: Argument 1 of the XML data type method "value" must be a string literal RRS feed

  • Question

  • Ok, been flying through the forum postings and seen this before, but just can't get it whipped in my particular scenerio:

    DECLARE @SQLSTRING nvarchar(600), @ParmDefinition nvarchar(600)
    SET @ParmDefinition = N'@vari varchar(255)';
    
    
        SET @SQLSTRING = N'Select 
        EventMessageID, EventData.value(''data((/LVEventData//ActivityDescription)[1])'',''varchar(500)'') as ActivityDescription
        ,EventData.value(''data((/LVEventData//Item1)[1])'',''varchar(30)'') as ActivityDttm
    	from event.EventMessage em
    	where em.[EventData].value(''data((/LVEventData/Items/Item/Name[.='' + @vari + ''])[1])'',''nvarchar(25)'') = ''FilesReceivedId''
    	AND em.[EventData].value(''data((/LVEventData/Items/Item/Value[.=''''892''''])[1])'',''nvarchar(25)'') = 892
    	order by EventMessageID desc'
    	
    	DECLARE @vari2 nvarchar(500)
    	SET @vari2 = N'FilesReceivedId'
    	
    	EXECUTE sp_executesql @SQLSTRING, @ParmDefinition,
    							@vari = @vari2;
    : (
    おろ?
    Wednesday, September 30, 2009 8:58 PM

Answers

  • You are right. You need to pass a string literal to the value(). So expressions such as
    value('data((/LVEventData/Items/Item/Name[.=' + @vari + '])[1]','INT')
    are invalid.

    The correct way to match the value of an element with a variable is by using the sql:variable() function. Here is the corrected version of the above code.
    value('data((/LVEventData/Items/Item/Name[.= sql:variable("@vari")])[1]','INT')


    Beyond Relational
    SyntaxHelp.com
    • Marked as answer by P.Brian.Mackey Thursday, October 1, 2009 1:49 PM
    Thursday, October 1, 2009 4:09 AM

All replies

  • You are right. You need to pass a string literal to the value(). So expressions such as
    value('data((/LVEventData/Items/Item/Name[.=' + @vari + '])[1]','INT')
    are invalid.

    The correct way to match the value of an element with a variable is by using the sql:variable() function. Here is the corrected version of the above code.
    value('data((/LVEventData/Items/Item/Name[.= sql:variable("@vari")])[1]','INT')


    Beyond Relational
    SyntaxHelp.com
    • Marked as answer by P.Brian.Mackey Thursday, October 1, 2009 1:49 PM
    Thursday, October 1, 2009 4:09 AM
  • Jacob YOU ARE AWESOME!!!!!!!
    おろ?
    Thursday, October 1, 2009 1:49 PM
  • but Jacob, what if I'm not trying to match anything but just specify a path? I've tried:

    .value('sql:variable("@path")', 'int')

    .value('(sql:variable("@path"))[1]', 'int')

    .value('(sql:variable("@path")/text())[1]', 'int')

    but nothing seems to work


    Do what thou wilt shall be the whole of the law -- AL. I. 40

    Thursday, September 13, 2012 12:14 AM
  • Consider starting a new thread.  Show some sample XML and expected results.
    Thursday, September 13, 2012 6:40 AM
    Answerer
  • wBob, I took your advice but posted a different (though related) question.  If I can figure out the answer to this: http://goo.gl/39HDC thenI can answer my question above.

    Do what thou wilt shall be the whole of the law -- AL. I. 40

    Monday, September 17, 2012 4:06 PM