The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal

The argument 1 of the XML data type method value must be a string literal

You must use sql variable implicitly:

Declare @Count Int = 1
While(@count <= @j)
Begin
insert into mytable
([Word])
Select ([XmlColumn].value('(/word[sql:variable("@Count")]/@Entry)[1]','nvarchar(max)'))
    from OtherTable WHERE ID=2

The argument 1 of the XML data type method query must be a string literal

You will need the sql:variable() XQuery extension function to refer to a variable. This function (quote from the link) "exposes a variable that contains a SQL relational value inside an XQuery expression".

Select @xml.query('/row[Id=sql:variable("@ID")]');

The argument 1 of the XML data type method nodes must be a string literal

You are trying to bind relational data from cmsContentType.Alias to xml element names. The reason for the error is you are constructing the xquery path via string concatenation which is not going to work.

To reference the column from your xquery you need to use the sql:column() function. A simple example is below, and more info here.

declare @tab table (Alias varchar(100));
insert into @tab
    select 'one' union all
    select 'two'

declare @x xml;
set @x = '<root><one updateDate="01-01-2014" /><two updateDate="01-01-2013" /></root>';

select  Alias,
        @x.value('(//*[local-name(.)=sql:column("t.Alias")])[1]/@updateDate', 'datetime')
from    @tab t;

This will get you the first element beneath <root> which joins to your Alias value, then it retrieves an attribute value (updateDate).

Let us know if this helps.

The argument 1 of the XML data type method value must be a string literal

with help from wBob on the Microsoft site, I've now got a clean solution. Performance is, of course, a concern as the whole document will get mapped for the sake of a single path but improvements are left as suggestion possibilities for the reader :)

if object_id('VMConfigVal') is not null
drop function VMConfigVal
go
create function VMConfigVal(@x xml, @path varchar(max))
returns nvarchar(max)
as
begin
    declare @ret nvarchar(max)

    ;with cte as
    (
    select  value = x.c.value('.', 'varchar(50)')
    ,       path = cast ( null as varchar(max) )
    ,       node = x.c.query('.')
    from    @x.nodes('/*') x(c)
    union all
    select  n.c.value('.', 'varchar(50)')
    ,       isnull( c.path + '/', '/' )
        +       n.c.value('local-name(.)', 'varchar(max)')
    ,       n.c.query('*')
    from    cte c
    cross   apply c.node.nodes('*') n(c)
    )
    select @ret = value from cte where path = @path
    return @ret
    end
go

so I can now do something like:

select dbo.VMConfigVal(MyXMLConfig, '/hardware/devices/IDE/ChannelCount')
from someTable

sweet!

XML data type method “value” must be a string literal

You cannot concatenate variables as strings in this way for the value method. You need to use sql:variable("@VariableName").

So your example would be something like this:

Declare @Count Int = 1 
While(@count <= @j) 
Begin 
insert into mytable 
([Word]) 

Select ([XmlColumn].value(N'/word[sql:variable("@Count")]/@Entry)[1]','nvarchar(max)'))
    from OtherTable WHERE ID=2

SQL XQuery - The argument 1 of the XML data type method modify must be a string literal

declare @xml xml = N'
<ElecTariffElements>
<ThresholdMatrix>
<Thresholds>
<BlockThreshold>1</BlockThreshold>
<BlockThreshold>2</BlockThreshold>
<BlockThreshold>3</BlockThreshold>
<BlockThreshold>4</BlockThreshold>
<BlockThreshold>4</BlockThreshold>
</Thresholds>
</ThresholdMatrix>
</ElecTariffElements>
';

declare @idx int = 1;
declare @NumThresholds int = @xml.value('count(//*:ElecTariffElements/*:ThresholdMatrix/*:Thresholds/*:BlockThreshold)', 'int');
select @NumThresholds, @xml;

while (@idx <= @NumThresholds)
begin
    set @xml.modify('replace value of (//*:ElecTariffElements/*:ThresholdMatrix/*:Thresholds/*:BlockThreshold/text())[sql:variable("@idx")][1] with 0');
    select @xml;
    set @idx = @idx + 1;
end

select @xml;

---

Related Topics