如何修改SQL XQuery语句提取XML中的ResultValue元素值？

现有如下SQL查询语句：

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
       SELECT
             Res.Expr.value('(../DMF:ResultDetail/Operator/Attribute/@ResultValue)[1]', 'nvarchar(150)') AS [Trying_to_Show_ResultValue_from_XML_Below]
             ,CAST(Expr.value('(../DMF:ResultDetail)[1]', 'nvarchar(max)')AS XML) AS ResultDetail
       FROM [SomeTable_With_EvaluationResults_XML_Column] AS PH
       CROSS APPLY EvaluationResults.nodes('
      declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
      //TargetQueryExpression'
       ) AS Res(Expr)

查询返回的ResultDetail列XML结构如下：

<Operator>
  <Attribute>
    <?char 13?>
    <TypeClass>DateTime</TypeClass>
    <?char 13?>
    <Name>LastBackupDate</Name>
    <?char 13?>
    <ResultObjType>System.DateTime</ResultObjType>
    <?char 13?>
    <ResultValue>638320511970000000</ResultValue>
    <?char 13?>
  </Attribute>
</Operator>

需要修改SELECT子句的第一列，正确提取ResultValue的值。

解决方案 #

你之前的写法有两处错误：

• ResultValue是<Attribute>的子元素，不是<Attribute>的属性，不能用@ResultValue（@是SQL XML查询里的属性选择器），要选择元素的文本内容。
• 整个XML的默认命名空间是http://schemas.microsoft.com/sqlserver/DMF/2007/08，所以<Operator>、<Attribute>、<ResultValue>这些元素都属于该命名空间，必须加上DMF:前缀才能正确引用。

修改后的第一列代码：

Res.Expr.value('(../DMF:ResultDetail/DMF:Operator/DMF:Attribute/DMF:ResultValue/text())[1]', 'nvarchar(150)') AS [Trying_to_Show_ResultValue_from_XML_Below]

完整的查询语句：

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
SELECT
      Res.Expr.value('(../DMF:ResultDetail/DMF:Operator/DMF:Attribute/DMF:ResultValue/text())[1]', 'nvarchar(150)') AS [Trying_to_Show_ResultValue_from_XML_Below]
      ,CAST(Expr.value('(../DMF:ResultDetail)[1]', 'nvarchar(max)')AS XML) AS ResultDetail
FROM [SomeTable_With_EvaluationResults_XML_Column] AS PH
CROSS APPLY EvaluationResults.nodes('
   declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
   //TargetQueryExpression'
) AS Res(Expr)

如果需要把这个Ticks值转换成可读的DateTime，可以这样处理：

Res.Expr.value('dateadd(ms, convert(bigint, ../DMF:ResultDetail/DMF:Operator/DMF:Attribute/DMF:ResultValue/text()[1]) / 10000, ''0001-01-01'')', 'datetime') AS [LastBackupDate]

（注：.NET的Ticks是从0001-01-01开始的100纳秒间隔，转换时需要除以10000转成毫秒，再用dateadd计算）

内容的提问来源于stack exchange，提问作者Stackoverflowuser