CODEWITHSUNDEEP
sql-serverxpathxqueryxquery-sql
Stackoverflowuser
Stackoverflowuser

Reputation: 243

XQuery Pull Data in Attribute

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?

Upvotes: 0

Views: 40

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, EvaluationResults XML);
INSERT @tbl (EvaluationResults) VALUES
(N'<DMF:ResultDetail xmlns:DMF="http://schemas.microsoft.com/sqlserver/DMF/2007/08">
    <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>
</DMF:ResultDetail>');
-- DDL and sample data population, end

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
SELECT t.ID
    , c.value('(Attribute/ResultValue/text())[1]', 'VARCHAR(50)') AS ResultValue 
    , c.query('.') AS ResultDetail 
FROM @tbl AS t
CROSS APPLY EvaluationResults.nodes('/DMF:ResultDetail/Operator') AS t1(c);

Output

ID ResultValue ResultDetail
1 638320511970000000 <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>

Upvotes: 0

Related Questions