SQL Server XQuery Pull Data in Attribute

mfpqipee 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(92)

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.

sql-server

来源：https://stackoverflow.com/questions/77237038/xquery-pull-data-in-attribute

1条答案

按热度按时间

lrpiutwd1#

A minimal reproducible example is not provided. So, I am shooting from the hip.

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>`

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server XQuery Pull Data in Attribute

1条答案

相关问题

热门标签

最新问答