CODEWITHSUNDEEP
sqlsql-serverxmlxquery
RekcsGaming
RekcsGaming

Reputation: 153

The target of 'replace' must be at most one node, found 'text *'

I'm trying to modify an XML value but keep getting the message

XQuery [myTable.XMLData.modify()]: The target of 'replace' must be at most one node, found 'text *'

<XMLData>
  <Range>60 Kg</Range>
  <Calibration>External Verification</Calibration>
</XMLData>

The query I am using for this purpose

update o set XMLData.modify('replace value of (/XMLData/Calibration/text()) with "Internal Verification"')
from myTable o
where cast(o.XMLDataas nvarchar(max)) like '%<Range>60 Kg</Range>%'

Can't understand what I am doing wrong.

Upvotes: 2

Views: 2324

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Please try the following solution.

Two points to mention:

  • It is using a variable @var for flexibility.
  • It is better to use XQuery XPath predicate instead of WHERE clause with LIKE ...

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<XMLData>
    <Range>60 Kg</Range>
    <Calibration>External Verification</Calibration>
</XMLData>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @var VARCHAR(30) = 'Internal Verification';

UPDATE @tbl
SET xmldata.modify('replace value of (/XMLData[Range/text()="60 Kg"]/Calibration/text())[1]
   with (sql:variable("@var"))' );

-- test
SELECT * FROM @tbl;

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

The target of 'replace' must be at most one node, found 'text *'

Means the XPath expression might match multiple nodes, but replace doesn't allow that. So you need an expression that matches no more than one node, eg:

declare @doc xml = '<XMLData>
  <Range>60 Kg</Range>
  <Calibration>External Verification</Calibration>
</XMLData>'

drop table if exists #t 
select @doc XMLData into #t

update o set XMLData.modify('replace value of (/XMLData/Calibration/text())[1] with "Internal Verification"')
from #t o
where o.XMLData.value('(/XMLData/Range)[1]','varchar(20)') = '60 Kg'

select * from #t

Upvotes: 2

Related Questions