Error: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content

Question

I have the following xml field in which I need to replace the value of one of the nodes:

<DataFormItem xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="bb_appfx_dataforms">
  <Values>
    <fv ID="FUNDRAISERID">
      <Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">1009fb5a-41a0-40c1-8685-d5973fb71df7</Value>
      <ValueTranslation>John Smith</ValueTranslation>
    </fv>
    <fv ID="STARTDATE">
      <Value xsi:type="xsd:dateTime">2015-01-01T00:00:00</Value>
    </fv>
  </Values>
</DataFormItem>

I need to change the STARTDATE value '2015-01-01T00:00:00' to '2016-01-01T00:00:00'. To accomplish this, I have written the following query:

update KPIINSTANCE
SET     NAME = '2016 ' + SUBSTRING(k.name, 6,150), 
        PARAMETERSXML.modify('
        declare namespace df = "bb_appfx_dataforms";
        replace value of (/df:DataFormItem/df:Values/df:STARTDATE/df:Value)[1] with "2016-01-01T00:00:00" cast as xs:dateTime ?')
FROM KPIINSTANCE K
        JOIN KPICATALOG KP ON KP.ID = K.KPICATALOGID
where k.ID = '43C6DA6B-420A-4D4E-BA31-84C8054B4AB6'

As far as I can tell, this is correct, but when I try to run the query, I get the error: XQuery [KPIINSTANCE.PARAMETERSXML.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(df{bb_appfx_dataforms}:Value,xdt:untyped) ?'

Since this node is typed, I'm unsure why the error is saying it is not typed. Anyone have any ideas?

It's an interesting problem according to the post by dbaspot here: http://dbaspot.com/sqlserver-programming/396989-xml-update.html "I don't believe it matters whether there is only one such element in your XML; I reckon it is parsing the XPath first and doesn't believe it's guaranteed to result in exactly one instance and therefore generates the error. I've even seen this when my schema guarantees that there is only one such element and it makes no difference " — Fuzzy, Jan 15 '16 at 19:40

Shnugo · Accepted Answer · 2016-01-18T11:51:19.413

Your XPath is wrong... STARTDATE is not a node's name but it's an attribut's name below "fv"

I think this is much simpler to solve: The nodes in question don't even have a namespace prefix. Try it like this:

PARAMETERSXML.modify('replace value of (/DataFormItem/Values/fv[@ID="STARTDATE"]/Value/text())[1] with "2016-01-01T00:00:00"')

I tested like this:

DECLARE @KPIINSTANCE TABLE(ID UNIQUEIDENTIFIER, NAME VARCHAR(100),PARAMETERSXML XML);
INSERT INTO @KPIINSTANCE VALUES
('43C6DA6B-420A-4D4E-BA31-84C8054B4AB6'
,'test'
,'<DataFormItem xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="bb_appfx_dataforms">
  <Values>
    <fv ID="FUNDRAISERID">
      <Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">1009fb5a-41a0-40c1-8685-d5973fb71df7</Value>
      <ValueTranslation>John Smith</ValueTranslation>
    </fv>
    <fv ID="STARTDATE">
      <Value xsi:type="xsd:dateTime">2015-01-01T00:00:00</Value>
    </fv>
  </Values>
</DataFormItem>'
);

SELECT * FROM @KPIINSTANCE;


UPDATE @KPIINSTANCE
SET     NAME = '2016 ' + 'test', 
        PARAMETERSXML.modify('replace value of (/DataFormItem/Values/fv[@ID="STARTDATE"]/Value/text())[1] with "2016-01-01T00:00:00"')
FROM @KPIINSTANCE K
        --JOIN KPICATALOG KP ON KP.ID = K.KPICATALOGID
WHERE K.ID = '43C6DA6B-420A-4D4E-BA31-84C8054B4AB6'

SELECT * FROM @KPIINSTANCE;

Error: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content

1 Answers1