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 http://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="http://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="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>')


go


 


INSERT INTO T VALUES (2,


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


go


You may notice that in the second example I used prefix “z” for the http://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="http://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 '@{http://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="http://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="http://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 http://www.microsoft.com/info/cpyright.htm.

Comments (1)
  1. Last time we looked at what happens to the xsi:nil attributes when replacing the value of a simply typed

Comments are closed.

Skip to main content