xml - 查询SQL Server 2016数据库中的XML数据

Question

我在SQL Server 2016数据库中存储了XML数据，我需要在存储过程中查询PROJECT_END附近的日期范围以返回application_ID

<PROJECTS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <APPLICATION_ID>3012448</APPLICATION_ID>
    <ACTIVITY>D23</ACTIVITY>
    <ADMINISTERING_IC>NU</ADMINISTERING_IC>
    <APPLICATION_TYPE>1</APPLICATION_TYPE>
    <ARRA_FUNDED xsi:nil="true" />
    <BUDGET_START>01/01/1985</BUDGET_START>
    <BUDGET_END>12/31/1985</BUDGET_END>
    <FOA_NUMBER xsi:nil="true" />
    <PROJECT_START>01/01/1985</PROJECT_START>
    <PROJECT_END>12/31/1987</PROJECT_END>
  </row>
</PROJECTS> 

我有选择部分，但似乎无法使用 PROJECT_END根据日期范围进行查询。 BETWEEN也可以吗？

SELECT 
    nref.value('APPLICATION_ID[1]', 'Int') APPLICATION_ID                         
FROM 
    STAGING
CROSS APPLY
    XMLData.nodes('/PROJECTS/row') AS r(nref)
WHERE
    nref.value('PROJECT_END[1]', 'varchar(max)') > '1/1/1987' 
    AND nref.value('PROJECT_END[1]', 'varchar(max)') < '1/1/1990' 

我无法控制日期字段的格式，除非可以即时进行转换。任何帮助表示赞赏。

Answer 1

如果您有日期值-为什么不这样处理呢？不要将此值转换为nvarchar(max)-这是一个日期，因此请将其转换为DATE！这样做，您的查询应该可以正常工作。

另外，我建议始终使用改编的ISO-8601格式将日期指定为字符串文字-例如格式YYYYMMDD（无斜杠，无）-适用于所有语言/日期格式设置：

SELECT 
    nref.value('APPLICATION_ID[1]', 'INT') APPLICATION_ID                         
FROM 
    STAGING
CROSS APPLY
    XMLData.nodes('/PROJECTS/row') AS r(nref)
WHERE
    nref.value('PROJECT_END[1]', 'DATE') > '19870101' 
    AND nref.value('PROJECT_END[1]', 'DATE') < '19900101'