XMLData.insert problem

[Peager]

Given an SQL XML table column with contents like this:

<fld>
  <state>WA</state>
  <taxcert>A18 3865 10</taxcert>
  <effective>1/1/2010</effective>
  <certexp_yn>Y</certexp_yn>
  <expires>12/31/2010</expires>
  <singleuse_yn>N</singleuse_yn>
  <exempt_yn>N</exempt_yn>
  <exempt_reason />
</fld>

I'm trying to modify it with an SQL query like this:

SELECT TOP 100
        [XMLData].modify('insert <entityusecode_id />
			  after (/fld)[1]
			 ')
FROM    [SCANREF] AS S

This SQL causes this error:

Msg 8137, Level 16, State 1, Line 39
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.
[/color red]

What am I doing wrong?

 

[tsuji]

Suppose the top 100 reference to some ordering per some column [id], something like this can do.
[tt]
UPDATE [SCANREF]
SET [XMLData].modify('insert <entityusecode_id /> after (/fld)[1]')
FROM [SCANREF] AS S,
(
SELECT TOP 100 [id], [XMLData] FROM [SCANREF] ORDER BY [id]
) AS T
WHERE S.[id]=T.[id]
go
[/tt]

 

[tsuji]

Looking at it again, simply this would do.
[tt]
UPDATE [SCANREF]
SET [XMLData].modify('insert <entityusecode_id /> after (/fld)[1]')
FROM
(
SELECT TOP 100 [id], [XMLData] FROM [SCANREF] ORDER BY [id]
) AS T
go
[/tt]

 

[Peager]

This works! Just a little too well. It isn't limited by the Top 100. What I was really trying to understand was why the .modify syntax would not work within a query. The MS docs seem to indicate that it should.

 

[tsuji]

If there is no intention to persist the modifying, a direct xquery would do. Like this. (I add a fictitious id and order by to make the use of top more proper. You have to change it according to your concrete realization.)
[tt]
SELECT TOP 100
[id],
[XMLData].[red]query('/fld[1], <entityusecode_id />') AS X[/red]
FROM [SCANREF] AS S [blue]ORDER BY [id][/blue]
[/tt]