patternsqlMinor

Replace value of strongly typed xml element in SQL Server with XQuery

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

xquerysqlwithreplacestronglyxmlvaluetypedelementserver

Problem

Given a element, defined within an XML Schema Collection as such:

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 "".

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!

Solution

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'
    
        
            
                
                    
                        
                            
                        
                    
                
            
        
    
';

go

declare @X xml(document dbo.SimpleTypeTest) = '1';

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
"".

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'
    
        
            
                
            
        
    
    
        
            
        
    
';

go

declare @X xml(document dbo.SimpleTypeTest) = '1';

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'
    
        
            
                
                    
                        
                            
                        
                    
                
            
        
    
';

go

declare @X xml(document dbo.SimpleTypeTest) = '1';
declare @X2 xml = @X;

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

Code Snippets

drop xml schema collection dbo.SimpleTypeTest;

go

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

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

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

drop xml schema collection dbo.SimpleTypeTest;

go

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

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

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

drop xml schema collection dbo.SimpleTypeTest;

go

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

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';
declare @X2 xml = @X;

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

Context

StackExchange Database Administrators Q#115599, answer score: 7

Revisions (0)

No revisions yet.