xsi:nil magic (part 1/2)

A few months ago I wrote a post about the interesting behavior of the xsi:type attribute. Today we’re going to look at his no so distant relative xsi:nil.

But first let’s do a quick recap. Any element can be made nillable by adding the attribute nillable=”true” to its declaration. Practically it means that this element can take a special value we call “nil”. An instance of this element will be “nilled” if it has no content and contains the boolean attribute nil from namespace https://www.w3.org/2001/XMLSchema-instance (usually associated to the prefix “xsi” but this is not a requirement) with a value of “true”.

As an example, let’s look at the following schema collection.

CREATE XML SCHEMA COLLECTION SCnil 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:simpleType name="ST">

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

                  <xs:minInclusive value="0"/>

            </xs:restriction>

      </xs:simpleType>

      <xs:element name="E_ST" type="ST" nillable="true"/>

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

</xs:schema>

'

go

 

Now if we create a table with a typed XML column we can easily create and store nilled instances of elements E_ST and E_CT_e

CREATE TABLE T (iCol int primary key, xmlCol XML(SCnil))

go

INSERT INTO T VALUES (1,

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

go

INSERT INTO T VALUES (2,

'<E_CT_e xmlns="urn:nil" xmlns:z="https://www.w3.org/2001/XMLSchema-instance" z:nil="true"/>')

go

You may notice that in the second example I used prefix “z” for the https://www.w3.org/2001/XMLSchema-instance namespace. As I said earlier “xsi” is the commonly used prefix but it is by no means the required one.

 

Now let’s look what being “nilled” means for a simply typed element.

If you query the data of a nilled simply typed element, the result will be the empty sequence. You can verify this by running the following query

SELECT xmlCol.query('declare namespace ns="urn:nil"; data(/ns:E_ST[1])instance of empty()')

FROM T WHERE iCol = 1

The result is “true”.

Now how do we “un-nil” such an element? We could try to delete the xsi”nil attribute but this is not allowed. A query such as

UPDATE T SET xmlCol.modify('declare namespace xsi="https://www.w3.org/2001/XMLSchema-instance"; delete /*[1]/@xsi:nil')

WHERE iCol = 1

will return the following error message

XQuery [T.xmlCol.modify()]: The XQuery syntax '@{https://www.w3.org/2001/XMLSchema-instance}:nil' is not supported.

The solution is to update the value of the element, like this

UPDATE T SET xmlCol.modify('declare namespace ns="urn:nil"; replace value of /ns:E_ST[1] with 1.0 cast as ns:ST?')

WHERE iCol = 1

go

SELECT xmlCol FROM T WHERE iCol = 1

go

The XML instance now looks like this

<E_ST xmlns="urn:nil" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">1</E_ST>

Notice that the xsi:nil attribute is no longer present.

The reverse operation consists in updating the element’s value with the empty sequence, like this

UPDATE T SET xmlCol.modify('declare namespace ns="urn:nil"; replace value of /ns:E_ST[1] with ()')

WHERE iCol = 1

go

SELECT xmlCol FROM T WHERE iCol = 1

go

The XML instance now looks like this

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

The element’s content is gone and attribute xsi:nil has been added with the Boolean value “true”.

 

I’m going to stop here for today. In the next installment we’ll look at what happens with complex content elements.

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