时间戳

首页 > 解决方案 > 如何将 GETDATE() 函数与 xml exists() 函数一起使用

sql - 如何将 GETDATE() 函数与 xml exists() 函数一起使用

问题描述

我有一个场景，我必须在查询的 where 子句中使用 xml exists() 函数。我必须将xml中的日期与当前日期进行比较。当我尝试使用 GETDATE() 函数时，出现以下错误。

XML 数据类型方法“exist”的参数 1 必须是字符串文字。

请考虑以下查询以供参考。

SELECT 
       TRY_CONVERT(datetime,NULLIF(t.x.value('(./Expire)[1]','varchar(max)'), '')) as expiration_date
FROM VW_Analytics_Base_Facts(nolock) BaseFact
CROSS APPLY BaseFact.Fact.nodes ('/Fact/Grant') t(x)
WHERE TYPE = '/asset/portfolio/option' 
AND BaseFact.Fact.exist('./Expire[(text()[1] cast as xs:date?) le xs:date("' + CONVERT(NVARCHAR(max),CONVERT(date,GETDATE()))+'")]')=1

提前致谢。

以下是更新的工作查询。

DECLARE @tbl TABLE (XmlCol xml)
INSERT INTO @tbl VALUES 
('<option>
            <OptionName>Option 1</OptionName>
            <grant>
                    <GrantName>Grant 1</GrantName>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>1/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>2/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>3/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                            </schedules>
            </grant>
            <grant>
                            <GrantName>Grant 2</GrantName>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>1/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>2/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>3/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                            </schedules>
            </grant>
        </option>'
)



SELECT e.XmlCol.value('(/option/OptionName)[1]', 'varchar(100)'),
        t.x.value('../.././GrantName[1]','varchar(100)') GrantName,
       t.x.value('(./scheduleDate)[1]', 'varchar(100)') scheduleDate
FROM @tbl e
       CROSS APPLY (SELECT CONVERT(date,GETDATE())) dt(today)
    cross apply e.XmlCol.nodes ('/option/grant/schedules/schedule') t(x)
WHERE e.XmlCol.exist('./scheduleDate[(text()[1] cast as xs:date?) le sql:column("dt.today")]')=1

我想根据计划日期过滤记录。此查询是视图的一部分，因此我无法声明变量。

标签： sqlxml

解决方案

下次请附加一个带有 DDL、数据插入、您自己的代码和预期输出的独立工作示例。这将允许测试解决方案。

在这种情况下，我必须猜测并发布未经测试的内容：

SELECT 
       TRY_CONVERT(datetime,NULLIF(t.x.value('(./Expire)[1]','varchar(max)'), '')) as expiration_date
FROM VW_Analytics_Base_Facts(nolock) BaseFact
CROSS APPLY (SELECT CONVERT(date,GETDATE())) dt(today)
CROSS APPLY BaseFact.Fact.nodes ('/Fact/Grant') t(x)
WHERE TYPE = '/asset/portfolio/option' 
AND BaseFact.Fact.exist('./Expire[(text()[1] cast as xs:date?) le sql:column("dt.today")]')=1;

此查询将使用更多CROSS APPLY，以便将信息包含在结果集中。该函数sql:column()允许在XQuery不违反must-be-a-literal规则的情况下使用结果集的列（用于sql:variable()获取已声明变量的值）。

更新

您尽早应用过滤器的想法没有错。这在内作为谓词.nodes()甚至更好。但是您必须依赖隐式强制转换，这是您不应该做的事情。

该表达式(text()[1] cast as xs:date?)将使用系统的文化。类似的值1/3/2018可能被视为 3 月 1 日或 1 月 3 日。因此，我建议将值读取为字符串并使用CONVERT适当的样式：

顺便说一句：我使用两次CROSS APPLYwith.nodes()来避免向后导航（../../）

WITH DerivedTable AS
(
    SELECT e.XmlCol.value('(/option/OptionName/text())[1]', 'varchar(100)') AS OptionName
          ,gr.value('(GrantName/text())[1]','varchar(100)') GrantName
          ,sch.value('(scheduleID/text())[1]', 'varchar(100)') scheduleID         --use appropriate type, might be "int"
          ,sch.value('(scheduleName/text())[1]', 'varchar(100)') scheduleName
          ,CONVERT(DATE,sch.value('(scheduleDate/text())[1]', 'varchar(100)'),110) scheduleDate     --fetch this as varchar, to avoid implicit casts
                                                                                                    --use CONVERT with the appropriate style to get a real date
                                                                                                    --110 is mdy, 103 is dmy
          ,sch.value('(scheduleAmount/text())[1]', 'varchar(100)') scheduleAmount --use appropriate type, might be "decimal(12,4)"
    FROM @tbl e
    CROSS APPLY e.XmlCol.nodes ('/option/grant') A(gr)
    CROSS APPLY A.gr.nodes('schedules/schedule') B(sch)
)
SELECT dt.*
FROM DerivedTable dt
--use a simple WHERE here

这会将 XML 的内容作为派生表返回。使用一个简单WHERE的来过滤这个派生表。

推荐阅读