CODEWITHSUNDEEP
sqlxmlsql-server-2012xquery
Phil Williams
Phil Williams

Reputation: 23

The target of 'replace' must be at most one node

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

The target of the replace must be at most one node, found attribute(prefType, xdt:untypedAtomic)

First here is my XML

<preferences>
 <categories>
<category id="1" prefType="2">
  <subcat id="1" prefType="2" />
  <subcat id="2" prefType="2" />
  <subcat id="3" prefType="2" />
  <subcat id="77" prefType="2" />
</category>
<category id="2" prefType="2">
  <subcat id="9" prefType="2" />
  <subcat id="10" prefType="2" />
  <subcat id="11" prefType="2" />
  <subcat id="12" prefType="2" />
  <subcat id="13" prefType="2" />
  <subcat id="14" prefType="2" />
  <subcat id="17" prefType="2" />
  <subcat id="78" prefType="2" />
  <subcat id="101" prefType="2" />
</category>
<category id="3" prefType="2">
  <subcat id="18" prefType="2" />
  <subcat id="19" prefType="2" />
  <subcat id="20" prefType="2" />
</category>
</categories>
</preferences>

And my code

declare @XMLinput as XML;
declare @custXML as XML;

declare @subcatid as nvarchar(3);
declare @interest as nvarchar(8);
declare @newValue as varchar(1);
declare @cnt as int;

set @XMLinput = '<preferences><categoryId>73</categoryId><interestLevel>POSITIVE</interestLevel></preferences>';

-- get the subcatid and interest level
SET @subcatid =  @XMLinput.value('(//preferences/categoryId)[1]','nvarchar(3)');
SET @interest =  @XMLinput.value('(//preferences/interestLevel)[1]','nvarchar(20)');

SET @newValue = 
CASE @interest
    WHEN 'POSITIVE' THEN '1'
    WHEN 'NEGATIVE' THEN  '3'
    ELSE '2'
END;

set @custXML = (select Preferences from Customer_Preferences where custID=11584);
select @custXML.exist('//preferences/categories/category/subcat[@id=sql:variable("@subcatid")]');

if (@@ROWCOUNT > 0)

BEGIN TRY
    BEGIN TRAN;
    set @cnt = CAST(CAST(@custXML.query('count(//preferences/categories/category/subcat[@id=(sql:variable("@subcatid"))])') AS VARCHAR) AS INT);

    -- replace the value
    UPDATE Customer_Preferences
        SET preferences.modify('
            replace value of 
                (//*/subcat[@id=sql:variable("@subcatid")]/@prefType[1]) 
            with sql:variable("@newValue")
            ')
    where CustID = 11584;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    select XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
    if @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

END CATCH

select preferences from Customer_Preferences where custid=11584
SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT AS '@@TRANCOUNT'

I've tried removing the sql variables and replacing them with fixed values but still get the same issue. I've also tried removing all the XML subcats except for one and the same error occurs.

After 3 hours of working through this and getting no where, I'd really appreciate your help.

Upvotes: 2

Views: 3957

Answers (1)

dirkk
dirkk

Reputation: 6218

Just as further reference (although you solved your problem already on your own) for others with similar problems: Here is what actually went wrong: The error message already indicates that the replace target can be at most one value (meaning you can replace only one value at a time).

However, (//*/subcat[@id=sql:variable("@subcatid")]/@prefType[1]) yields a sequence of results What is means literally is to take each subcat element and select the first attribute with the name prefType. This actually does not make much sense as an XML element can not have multiple attributes with the same name, so the query would be the same without the [1] predicate.

What you probably wanted to write is: Give me each prefType attribute of each subcat element and return only the first one of the whole result set. That is exactly what your working query is doing: (//*/subcat[@id=sql:variable("@subcatid")]/@prefType)[1]

Upvotes: 2

Related Questions