我们应该在比较时转换 xml 的左侧操作数吗?

Question

此链接 https://learn.microsoft.com/en-us/sql/t-sql/xml/exist-method-xml-data-type 有以下代码：

declare @x xml;  
declare @f bit;  
set @x = '<root Somedate = "2002-01-01Z"/>';  
set @f = @x.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2002-01-01Z")]');  
select @f; 

上面的链接说

你可以使用 xs：date() 构造函数，而不是强制转换为 xs：date()。

所以我把它表述如下，请确认上面的一行的意思与我推断的相同。

那不是使用强制转换为 xs：date .

declare @x xml;  
declare @f bit;  
set @x = '<root Somedate = "2002-01-01Z"/>';  
set @f = @x.exist('/root[xs:date(@Somedate) eq xs:date("2002-01-01")]');  
select @f; 

你的真诚

Answer 1

我必须承认，我对这些结果有点困惑......

显然，在XQuery中，对DATE和DATETIME的解释要接近得多，然后我就预料到了。尤其是Equals4和Equals5的列从来都不是真的......

我学到的是，当你想比较日期或日期时间值时，必须非常小心......有些强制转换会中断，因为显然没有隐式截断日期时间......

试试这个：

declare @x xml;  
--Somedate1 is your Zone-format
--Somedate2 is a simple ISO8601 date
--Somedate3 is a simple ISO8601 dateTime
set @x = '<root Somedate1 = "2002-01-01Z"
Somedate2 = "2002-01-01"
Somedate3 = "2002-01-01T00:00:00"/>';  
--Compare it with a variable with the date as string (try without the "Z" too)
--and a variable with a "real" DATETIME (try with DATE too => Equals9 will not work anymore)
declare @DtAsString VARCHAR(100)='2002-01-01Z';  
declare @DtAsDateTime DATETIME={d'2002-01-01'};  
SELECT
SimpleData = @x.query('data(/root/@Somedate1)[1]')
,DateCtor   = @x.query('xs:date((/root/@Somedate1)[1])')
,DtTmCtor   = @x.query('xs:dateTime((/root/@Somedate1)[1])')
,CastToDate = @x.query('(/root/@Somedate1)[1] cast as xs:date?')
,ToDateTime = @x.query('(/root/@Somedate1)[1] cast as xs:dateTime?')
,Val2Date   = @x.value('(/root/@Somedate1)[1]','date')
,Val2DtTime = @x.value('(/root/@Somedate1)[1]','datetime')
,Equals1    = @x.exist('/root[@Somedate1 eq sql:variable("@DtAsString")]')
,Equals2    = @x.exist('/root[(@Somedate1) eq xs:date(sql:variable("@DtAsString"))]')
,Equals3    = @x.exist('/root[xs:date(@Somedate1) eq xs:date(sql:variable("@DtAsString"))]')
,Equals4    = @x.exist('/root[xs:dateTime(@Somedate1) eq xs:dateTime(sql:variable("@DtAsString"))]')
,Equals5    = @x.exist('/root[@Somedate1 cast as xs:dateTime? eq xs:dateTime(sql:variable("@DtAsString"))]')
,Equals6    = @x.exist('/root[xs:date(@Somedate1) eq xs:date(sql:variable("@DtAsString"))]')
,Equals7    = @x.exist('/root[@Somedate1 cast as xs:date? eq xs:date(sql:variable("@DtAsString"))]')
,Equals8    = @x.exist('/root[@Somedate1 cast as xs:date? eq sql:variable("@DtAsString") cast as xs:date?]')
,Equals9    = @x.exist('/root[@Somedate1 cast as xs:dateTime? eq sql:variable("@DtAsDateTime") cast as xs:dateTime?]')
UNION ALL SELECT
@x.query('data(/root/@Somedate2)[1]')
,@x.query('xs:date((/root/@Somedate2)[1])')
,@x.query('xs:dateTime((/root/@Somedate2)[1])')
,@x.query('(/root/@Somedate2)[1] cast as xs:date?')
,@x.query('(/root/@Somedate2)[1] cast as xs:dateTime?')
,@x.value('(/root/@Somedate2)[1]','date')
,@x.value('(/root/@Somedate2)[1]','datetime')
,@x.exist('/root[@Somedate2 eq sql:variable("@DtAsString")]')
,@x.exist('/root[(@Somedate2) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:date(@Somedate2) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:dateTime(@Somedate2) eq xs:dateTime(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate2 cast as xs:dateTime? eq xs:dateTime(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:date(@Somedate2) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate2 cast as xs:date? eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate2 cast as xs:date? eq sql:variable("@DtAsString") cast as xs:date?]')
,@x.exist('/root[@Somedate2 cast as xs:dateTime? eq sql:variable("@DtAsDateTime") cast as xs:dateTime?]')
UNION ALL SELECT
@x.query('data(/root/@Somedate3)[1]')
,@x.query('xs:date((/root/@Somedate3)[1])')
,@x.query('xs:dateTime((/root/@Somedate3)[1])')
,@x.query('(/root/@Somedate3)[1] cast as xs:date?')
,@x.query('(/root/@Somedate3)[1] cast as xs:dateTime?')
,@x.value('(/root/@Somedate3)[1]','date')
,@x.value('(/root/@Somedate3)[1]','datetime')
,@x.exist('/root[@Somedate3 eq sql:variable("@DtAsString")]')
,@x.exist('/root[(@Somedate3) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:date(@Somedate3) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:dateTime(@Somedate3) eq xs:dateTime(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate3 cast as xs:dateTime? eq xs:dateTime(sql:variable("@DtAsString"))]')
,@x.exist('/root[xs:date(@Somedate3) eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate3 cast as xs:date? eq xs:date(sql:variable("@DtAsString"))]')
,@x.exist('/root[@Somedate3 cast as xs:date? eq sql:variable("@DtAsString") cast as xs:date?]')
,@x.exist('/root[@Somedate3 cast as xs:dateTime? eq sql:variable("@DtAsDateTime") cast as xs:dateTime?]')

结果

+---------------------+-------------+---------------------+-------------+---------------------+------------+-------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| SimpleData          | DateCtor    | DtTmCtor            | CastToDate  | ToDateTime          | Val2Date   | Val2DtTime              | Equals1 | Equals2 | Equals3 | Equals4 | Equals5 | Equals6 | Equals7 | Equals8 | Equals9 |
+---------------------+-------------+---------------------+-------------+---------------------+------------+-------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 2002-01-01Z         | 2002-01-01Z |                     | 2002-01-01Z |                     | 2002-01-01 | 2002-01-01 00:00:00.000 | 1       | 1       | 1       | 0       | 0       | 1       | 1       | 1       | 0       |
+---------------------+-------------+---------------------+-------------+---------------------+------------+-------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 2002-01-01          | 2002-01-01  |                     | 2002-01-01  |                     | 2002-01-01 | 2002-01-01 00:00:00.000 | 0       | 1       | 1       | 0       | 0       | 1       | 1       | 1       | 0       |
+---------------------+-------------+---------------------+-------------+---------------------+------------+-------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 2002-01-01T00:00:00 |             | 2002-01-01T00:00:00 |             | 2002-01-01T00:00:00 | 2002-01-01 | 2002-01-01 00:00:00.000 | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 1       |
+---------------------+-------------+---------------------+-------------+---------------------+------------+-------------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+