CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xsdxquery
Andrey Morozov
Andrey Morozov

Reputation: 7969

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

I have a schema with xs:date attribute which is defined in a way that it may contain date or be empty.

But when I trying to query this element I get an error

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

Any suggestions?

Steps to reproduce

create xml schema collection dbo.[test] AS 
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')
FROM @xml.nodes('/PACKAGE/CUSTOMER') as t(cust)

go

drop xml schema collection dbo.[test]

Upvotes: 3

Views: 266

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use the data Function (XQuery)

select
  BIRTHDAY = t.cust.value('data(BIRTHDAY)[1]', 'date')
FROM @xml.nodes('/PACKAGE/CUSTOMER') as t(cust)

The function value() in SQL Server needs a single value and BIRTHDAY is defined as a list of dates. (BIRTHDAY)[1] will give you the first list of dates. data(BIRTHDAY)[1] will give you the first date in the list of dates stored in BIRTHDAY.

Upvotes: 2

Andrey Morozov
Andrey Morozov

Reputation: 7969

Got it! (another way)

select
  BIRTHDAY = nullif(t.cust.query('BIRTHDAY').value('(BIRTHDAY)[1]', 'date'), '1900-01-01')
from @xml.nodes('/PACKAGE/CUSTOMER') as t(cust)

Results:

BIRTHDAY
----------
NULL
2010-01-01

Upvotes: 0

Related Questions