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.
Comments
- Anonymous
December 04, 2006
Last time we looked at what happens to the xsi:nil attributes when replacing the value of a simply typed