CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery-sql
bert
bert

Reputation: 35

SQL Server: is it possible to change the value of a complex XML element?

I need to change a value within a XML element - for this untyped version it works this way:

declare @X xml=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

set @X.modify('replace value of (/translations/value[@lang="en-US"]/text())[1] with "replacedValue"');

select @X.value('(/translations/value[@lang="en-US"])[1]','varchar(max)');

The select return the "replacedValue" for the "value" element with attribute lang="en-US".

Unfortunately I have to do this for a XML attribute in a database which is typed which the following XML schema:

CREATE XML SCHEMA COLLECTION [dbo].[LocaleSchema] AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="translations"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element name="value"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="lang" type="language" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:simpleType name="language"><xsd:restriction base="xsd:string"><xsd:enumeration value="de-DE" /><xsd:enumeration value="en-US" /></xsd:restriction></xsd:simpleType></xsd:schema>'

For better readability afterwards only the XML schema pretty-printed:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="translations">
        <xsd:complexType>
            <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                    <xsd:sequence minOccurs="0" maxOccurs="unbounded">
                        <xsd:element name="value">
                            <xsd:complexType>
                                <xsd:simpleContent>
                                    <xsd:extension base="xsd:string">
                                        <xsd:attribute name="lang" type="language" />
                                    </xsd:extension>
                                </xsd:simpleContent>
                            </xsd:complexType>
                        </xsd:element>
                    </xsd:sequence>
                </xsd:restriction>
            </xsd:complexContent>
        </xsd:complexType>
    </xsd:element>
    <xsd:simpleType name="language">
        <xsd:restriction base="xsd:string">
            <xsd:enumeration value="de-DE" />
            <xsd:enumeration value="en-US" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:schema>

As you can see here the "value" XML element is a complex type. According to the documentation the modify() function is only valid for simple types. (besides also the text() function is only valid for simple types)

So afterwards the SQL statements from above for the typed content - which causes an error when trying to modify:

declare @X xml (CONTENT [dbo].[LocaleSchema])=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

set @X.modify('replace value of (/translations/value[@lang="en-US"]/text())[1] with "replacedValue"');

Is there a suggestion to work around? Or any other possibility to change the XML attribute? (I need to use this in an UPDATE statement in real life of course)

Thank you in advance!

Upvotes: 0

Views: 404

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

This works...

CREATE XML SCHEMA COLLECTION [dbo].[LocaleSchema] AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="translations"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element name="value"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="lang" type="language" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:simpleType name="language"><xsd:restriction base="xsd:string"><xsd:enumeration value="de-DE" /><xsd:enumeration value="en-US" /></xsd:restriction></xsd:simpleType></xsd:schema>';
GO

declare @X xml (CONTENT [dbo].[LocaleSchema])=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

set @X.modify('replace value of (/translations/value[@lang="en-US"])[1] with "replacedValue"');

SELECT @x;
GO

--clean up
--DROP XML SCHEMA COLLECTION dbo.LocaleSchema;

Upvotes: 1

Related Questions