Modify element value for typed and untyped xml

First, let’s define an untyped xml and modify it:

 

declare @x xml

SET @x=N‘

<Properties xmlns="http://www.xyz.com/p01"&gt;

      <Version>1.0.0</Version>

      <MyProperties>

      <Property>P1</Property>

      </MyProperties>

</Properties>’

/*

– comment 1

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version/text())[1] with  "2.0.0"’);

*/

/*

– comment 2

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "2.0.0"’);

*/

SELECT @x;

GO

Uncomment the first comment block, run the script, it works fine. The version numbe in the xml is modified with the new value.

Commnet the first comment block and uncomment the second block, run the sript it gives the following error:

Msg 2356, Level 16, State 1, Line 16

XQuery [modify()]: The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(p{http://www.xyz.com/p01&#125;:Version,xdt:untyped) ?’

Now let’s create an xml scehma in order to define a typed xml  

USE TEMPDB

GO

declare @xs xml

SET @xs=N‘

<xs:schema id="Test" targetNamespace="http://www.xyz.com/p01&quot;

      xmlns:xs="http://www.w3.org/2001/XMLSchema&quot;

      xmlns:p="http://www.xyz.com/p01&quot;

      attributeFormDefault="unqualified" elementFormDefault="qualified">

 

      <xs:simpleType name="typeVersion">

            <xs:restriction base="xs:string">

                  <xs:pattern value="[0-9]{1,2}\.([0-9]){1,2}\.([0-9]){1,2}" />

            </xs:restriction>

      </xs:simpleType>

 

      <xs:element name="Properties">

            <xs:complexType>

                  <xs:sequence>

                        <xs:element name="Version" type="p:typeVersion" />

                        <xs:element name="MyProperties">

                              <xs:complexType>

                                    <xs:sequence>

                                          <xs:element name="Property" type="xs:string" maxOccurs="unbounded" />

                                    </xs:sequence>

                              </xs:complexType>

                        </xs:element>    

                  </xs:sequence>

            </xs:complexType>

      </xs:element>

</xs:schema>

‘

 

CREATE XML SCHEMA COLLECTION dbo.TestSchema AS @xs;

GO

We will focus on the two elements Version and Property in the schema. Version is defined by a simple type p:typeVersion, while Property is defined as a W3C data type xs:string. We will see we need to use different method to modify the values of the two elements.

declare @x xml (dbo.TestSchema)

SET @x=N‘

<Properties xmlns="http://www.xyz.com/p01"&gt;

      <Version>1.0.0</Version>

      <MyProperties>

      <Property>P1</Property>

      </MyProperties>

</Properties>’

SELECT @x;

 

/*

– comment 1

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "P10"’);

 

SELECT @x;

*/

/*

– comment 2

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version/text())[1] with  "P10"’);

 

SELECT @x;

*/

 

/*

– comment 3

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "2.0.0" cast as p:typeVersion?’);

 

SELECT @x;

 

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:MyProperties/p:Property)[1] with  "P10"’);

 

SELECT @x;

*/

 

/*

– comment 4

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:MyProperties/p:Property/text())[1] with  "P10"’);

 

SELECT @x;

*/

 

Run comment 1 block (uncomment the block and run it), it gives the following error. It means the value “2.0.0” is of xs:string. The expected type is p:typeVersion.

Msg 2247, Level 16, State 1, Line 13

XQuery [modify()]: The value is of type "xs:string", which is not a subtype of the expected type "p{http://www.xyz.com/p01&#125;:typeVersion".

 

Run comment 2 block (comment other comment blocks and uncomment this block and run it), it gives the following error. It says text() is not supported for typed xml in modify function.

Msg 9312, Level 16, State 1, Line 20

XQuery [modify()]: ‘text()’ is not supported on simple typed or ‘http://www.w3.org/2001/XMLSchema#anyType&#8217; elements, found ‘element(p{http://www.xyz.com/p01&#125;:Version,p{http://www.xyz.com/p01&#125;:typeVersion) *’.

 

Run comment 3 block, it works fine. The code casts the xs:string “P10” to p:typeVersion. Note the cast syntax, it requires a question mask ? at the end. The modification for p:property also works because its value is xs:string.

Run comment 4 block, which adds text() after the element xpath. it gives the same error as comment 2 block.

Msg 9312, Level 16, State 1, Line 46

XQuery [modify()]: ‘text()’ is not supported on simple typed or ‘http://www.w3.org/2001/XMLSchema#anyType&#8217; elements, found ‘element(p{http://www.xyz.com/p01&#125;:Property,xs:string) *’.

 

To summary:

For untyped xml, to modify element value, we need to attach text() function after the elemnt xpath. Without text(), SQL server gives error;

For typed xml,  text() is not supported. Only the xpath without text() is reuqired;

If the element is of W3C “http://www.w3.org/2001/XMLSchema” data type, the value can be specified directly;

If the element is of user defined type, we need to use cast funtion to cast the value to the defined type:

"Value" cast as UserDefinedType?

 

About these ads