Replace value of strongly typed xml element in SQL Server with XQuery Given a element, defined within an XML Schema Collection as such:

&lt;xsd:element name="xid"&gt;
    &lt;xsd:simpleType&gt;
        &lt;xsd:restriction base="xsd:string"&gt;
            &lt;xsd:maxLength value="32" /&gt;
        &lt;/xsd:restriction&gt;
    &lt;/xsd:simpleType&gt;
&lt;/xsd:element&gt;


How would you update the element using XQuery?

The element is found within the ns namespace in the schema collection. I have been trying to update the element the below query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793" cast as element(ns{http://www.anon.com}:xid,#anonymous) ?') 
 where id = 11793


but this generates the following error:


  Msg 9301, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: In this version of the server, 'cast as
  ' is not available. Please use the 'cast as  ?' syntax.


If I remove the cast entirely and use this query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793"') 
 where id = 11793


I get this error:


  Msg 2247, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: The value is of type "xs:string", which
  is not a subtype of the expected type "&lt;anonymous&gt;".


If I issue this query:

update cm.item
   set data.modify(
      'declare namespace ns="http://www.anon.com/"; 
       replace value of (/ns:*/ns:xid/text())[1] with "X00011793"')
 where id = 11793


I get this error:


  Msg 9312, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: 'text()' is not supported on simple typed
  or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found
  '(element(ns{http://www.anon.com/}:xid,#anonymous) ?) *'.


I am targeting SQL Server 2008 R2.

Thanks!
I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions.

Simple repro of what you have:

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;xs:element name="root"&gt;
        &lt;xs:complexType&gt;
            &lt;xs:sequence&gt;
                &lt;xs:element name="xid"&gt;
                    &lt;xs:simpleType&gt;
                        &lt;xs:restriction base="xs:string"&gt;
                            &lt;xs:maxLength value="30"/&gt;
                        &lt;/xs:restriction&gt;
                    &lt;/xs:simpleType&gt;
                &lt;/xs:element&gt;
            &lt;/xs:sequence&gt;
        &lt;/xs:complexType&gt;
    &lt;/xs:element&gt;
&lt;/xs:schema&gt;';

go

declare @X xml(document dbo.SimpleTypeTest) = '&lt;root&gt;&lt;xid&gt;1&lt;/xid&gt;&lt;/root&gt;';

set @X.modify('replace value of /root/xid  with "2"');


Result:


  Msg 2247, Level 16, State 1, Line 25 XQuery [modify()]: The value is
  of type "xs:string", which is not a subtype of the expected type
  "&lt;anonymous&gt;".


One workaround is to modify your schema to use a named simple type xidType and cast the new value. 

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;xs:element name="root"&gt;
        &lt;xs:complexType&gt;
            &lt;xs:sequence&gt;
                &lt;xs:element name="xid" type="xidType"/&gt;
            &lt;/xs:sequence&gt;
        &lt;/xs:complexType&gt;
    &lt;/xs:element&gt;
    &lt;xs:simpleType name="xidType"&gt;
        &lt;xs:restriction base="xs:string"&gt;
            &lt;xs:maxLength value="30"/&gt;
        &lt;/xs:restriction&gt;
    &lt;/xs:simpleType&gt;
&lt;/xs:schema&gt;';

go

declare @X xml(document dbo.SimpleTypeTest) = '&lt;root&gt;&lt;xid&gt;1&lt;/xid&gt;&lt;/root&gt;';

set @X.modify('replace value of /root/xid  with "2" cast as xidType?');


Another way is to extract the XML to an untyped XML variable, modify the variable and put it back to the table.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;xs:element name="root"&gt;
        &lt;xs:complexType&gt;
            &lt;xs:sequence&gt;
                &lt;xs:element name="xid"&gt;
                    &lt;xs:simpleType&gt;
                        &lt;xs:restriction base="xs:string"&gt;
                            &lt;xs:maxLength value="30"/&gt;
                        &lt;/xs:restriction&gt;
                    &lt;/xs:simpleType&gt;
                &lt;/xs:element&gt;
            &lt;/xs:sequence&gt;
        &lt;/xs:complexType&gt;
    &lt;/xs:element&gt;
&lt;/xs:schema&gt;';

go

declare @X xml(document dbo.SimpleTypeTest) = '&lt;root&gt;&lt;xid&gt;1&lt;/xid&gt;&lt;/root&gt;';
declare @X2 xml = @X;

set @X2.modify('replace value of (/root/xid/text())[1]  with "2"');
set @X = @X2;