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

0
votes

I have the following snippet of SQL:

declare @idx int = 1

while (@idx <= @NumThresholds)
begin
    declare @strIdx nvarchar(100) = convert(varchar(100), @idx)
    declare @xqueryString nvarchar(max) = concat('replace value of (//*:ElecTariffElements/*:ThresholdMatrix/*:Thresholds/*:BlockThreshold/text())[', @strIdx, '] with "0"')
    set @xml.modify(@xqueryString)
    set @idx = @idx + 1
end

where I want to update each value of a BlockThreshold element.

However I get the error:

The argument 1 of the XML data type method "modify" must be a string literal.

I have also tried using the sql:variable("@strIdx") like so:

declare @idx int = 1

while (@idx <= @NumThresholds)
begin
    declare @strIdx nvarchar(100) = convert(varchar(100), @idx)
    set @xml.modify('replace value of (//*:ElecTariffElements/*:ThresholdMatrix/*:Thresholds/*:BlockThreshold/text())[sql:variable("@strIdx")] with "0"')
    set @idx = @idx + 1
end

but this then I get this error:

XQuery [modify()]: Only 'http://www.w3.org/2001/XMLSchema#decimal?', 'http://www.w3.org/2001/XMLSchema#boolean?' or 'node()*' expressions allowed as predicates, found 'xs:string ?'

I just want to iterate over the elements and replace the values but it seems to be a lot more difficult than I might expect it to be.

Any help is greatly appreciated!

1
sql-serverxquerystring-literals

1 Answers

1
votes
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;