sql server - How to xQuery an element that is a list of xs:date that can be empty? -

sql server - How to xQuery an element that is a list of xs:date that can be empty? -

i have schema xs:date attribute defined in way may contain date or empty.

but when trying query element error

"xquery [value()]: 'value()' requires singleton (or empty sequence), found operand of type 'xs:date *'"

any suggestions?

steps reproduce

create xml schema collection dbo.[test] n'<xs:schema xmlns:xs="http://www.w3.org/2001/xmlschema"> <xs:element name="package" > <xs:complextype> <xs:choice minoccurs="0" maxoccurs="unbounded"> <xs:element name="customer"> <xs:complextype> <xs:sequence> <xs:element name="birthday" > <xs:annotation> <xs:documentation>date of birth</xs:documentation> </xs:annotation> <xs:simpletype> <xs:restriction> <xs:simpletype> <xs:list itemtype="xs:date" /> </xs:simpletype> <xs:minlength value="0" /> <xs:maxlength value="1" /> </xs:restriction> </xs:simpletype> </xs:element> </xs:sequence> </xs:complextype> </xs:element> </xs:choice> </xs:complextype> </xs:element> </xs:schema>'; go declare @xml xml(dbo.[test]); set @xml = '<package> <customer> <birthday></birthday> </customer> <customer> <birthday>2010-01-01</birthday> </customer> </package>' select birthday = t.cust.value('(birthday)[1]', 'date') @xml.nodes('/package/customer') t(cust) go drop xml schema collection dbo.[test]

you can utilize data function (xquery)

class="lang-sql prettyprint-override">select birthday = t.cust.value('data(birthday)[1]', 'date') @xml.nodes('/package/customer') t(cust)

the function value() in sql server needs single value , birthday defined list of dates. (birthday)[1] give first list of dates. data(birthday)[1] give first date in list of dates stored in birthday.

sql-server xml sql-server-2008 xsd xquery