I have this query
;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)
which produces the second column ResultDetail of XML type
<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>
and I am trying to show the ResultValue attribute in the first column of the SELECT clause above. Any help how to build this line? Thank you.
1条答案
按热度按时间lrpiutwd1#
A minimal reproducible example is not provided. So, I am shooting from the hip.
SQL
Output
<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>