0

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!

bert
  • 35
  • 1
  • 7
  • Store the data in relational format instead of XML? – RichardCL Apr 28 '16 at 13:03
  • Read the XML field into an untyped XML variable, make the change, and update the table? This might be very inefficient, especially if XML indexes have to be updated. – RichardCL Apr 28 '16 at 13:05

1 Answers1

1

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;
Shnugo
  • 66,100
  • 9
  • 53
  • 114