xsi:nil magic (part 2/2)

Last time we looked at what happens to the xsi:nil attributes when replacing the value of a simply typed element. In this post, we’re going to look at complex types.

First let’s create a schema collection with complex types and elements.

CREATE XML SCHEMA COLLECTION SCnil_cplx AS '

<xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema" targetNamespace="urn:nil" xmlns="urn:nil">

      <xs:complexType name="CT_emptiable">

            <xs:choice minOccurs="0" maxOccurs="unbounded">

                  <xs:element name="a" type="xs:string"/>

                  <xs:element name="b" type="xs:byte"/>

            </xs:choice>

      </xs:complexType>

      <xs:complexType name="CT_nonEmptiable">

            <xs:choice minOccurs="1" maxOccurs="unbounded">

                  <xs:element name="a" type="xs:string"/>

                  <xs:element name="b" type="xs:byte"/>

            </xs:choice>

      </xs:complexType>

      <xs:element name="N1" type="CT_emptiable" nillable="true"/>

      <xs:element name="E1" type="CT_emptiable" nillable="false"/>

      <xs:element name="N2" type="CT_nonEmptiable" nillable="true"/>

      <xs:element name="E2" type="CT_nonEmptiable" nillable="false"/>

</xs:schema>'

go

CREATE TABLE T (idCol int primary key, xmlCol XML(SCnil_cplx))

go

Let’s insert 4 rows, containing instances of the 4 top level elements defined in the schema collection.

INSERT INTO Tcplx VALUES (1,'<ns:N1 xmlns:ns="urn:nil"><a>Data</a><b>2</b></ns:N1>')

INSERT INTO Tcplx VALUES (2,'<ns:N2 xmlns:ns="urn:nil"><a>Data</a><b>2</b></ns:N2>')

INSERT INTO Tcplx VALUES (3,'<ns:E1 xmlns:ns="urn:nil"><a>Data</a><b>2</b></ns:E1>')

INSERT INTO Tcplx VALUES (4,'<ns:E2 xmlns:ns="urn:nil"><a>Data</a><b>2</b></ns:E2>')

go

Now let’s see what happens when we delete all the children elements under each of those top level elements.

Let’s start with element ‘E1’ which is not nillable, and whose content model is emptiable (which means an empty element is valid with respect to the schema definition).

UPDATE Tcplx

SET xmlCol.modify('declare namespace ns="urn:nil"; delete /ns:E1[1]/*')

WHERE idCol = 3

go

SELECT xmlCol FROM Tcplx WHERE idCol = 3

go

The DML operation is successful and following its completion the instance looks like this.

<ns:E1 xmlns:ns="urn:nil" />

Let’s do the same thing with element ‘E2’ which is not nillable and whose content model is not emptiable (which means an empty element is invalid with respect to the schema)

UPDATE Tcplx

SET xmlCol.modify('declare namespace ns="urn:nil"; delete /ns:E2[1]/*')

WHERE idCol = 4

go

As could be expected this query fails with the following message:

XML Validation: Invalid content. Expected element(s): a,b. Location: /*:E2[1]

Now that we’ve established the behavior for non nillable elements let’s look at ‘N1’, which is nillable and whose content model is emptiable.

UPDATE Tcplx

SET xmlCol.modify('declare namespace ns="urn:nil"; delete /ns:N1[1]/*')

WHERE idCol = 1

go

SELECT xmlCol FROM Tcplx WHERE idCol = 1

go

The DML operation is successful and the instance now looks like this

<ns:N1 xmlns:ns="urn:nil" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

Notice that even though, an empty instance of N1 would have been valid with respect to the schema, when all the children element were deleted the server nilled the instance by adding the attribute xsi:nil with a value of “true”.

Running a similar query on an instance of ‘N2’, which is nillable and non emptiable also yields interesting results.

UPDATE Tcplx

SET xmlCol.modify('declare namespace ns="urn:nil"; delete /ns:N2[1]/*')

WHERE idCol = 2

go

SELECT xmlCol FROM Tcplx WHERE idCol = 2

go

Even though the element isn’t emptiable, the query succeeds, and the resulting instance looks like this:

<ns:N2 xmlns:ns="urn:nil" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

Once again, when all the children elements are deleted, the element is nilled by adding xsi:nil=”true”.

In conclusion, the behavior for elements with complex types is simple. If an element is nillable, removing its children elements will nill it through the addition of xsi:nil=”true”.

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.