XML 查詢問題

我正在嘗試生成一個 SQL 查詢以從以下 SQL XML 中獲取“DATE”值：

我已經嘗試過這樣的事情，但我認為我不理解這些概念。

select 
  xConfig.value('(/SearchjobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression) [1]','nvarchar(max)') 
from 
   Job 

這是作為文本的 XML：

<SearchJobConfig>
 <QueryID>1072</QueryID>
 <QueryString>
   <SearchCriteria name="Search query" >
     <ExpressionSet logicalOperator="AND">
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="GREATER_EQUAL" dataType="string" caseSensitive="false">2019-06-01T04:00:00</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="LESS_EQUAL" dataType="string" caseSensitive="false">2019-06-13T03:59:59</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Class" npmPropertyId="1056" searchOperation="EQUALS" dataType="int32" caseSensitive="false">65</SimpleAttributeExpression>
     </ExpressionSet>
   </SearchCriteria>
 </QueryString>
</SearchJobConfig>

預期的輸出將是日期：

2019-06-01T04:00:00
2019-06-13T03:59:59

以及在同一行上獲得結果需要什麼.. 範例：

date_val_start date_val_end 2019-06-01T04:00:00 2019-06-13T03:59:59

我正在使用 SQL Server 2012 企業版。

這能得到你想要的嗎？

DECLARE @x XML = '
<SearchJobConfig>
 <QueryID>1072</QueryID>
 <QueryString>
   <SearchCriteria name="Search query" >
     <ExpressionSet logicalOperator="AND">
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="GREATER_EQUAL" dataType="string" caseSensitive="false">2019-06-01T04:00:00</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="LESS_EQUAL" dataType="string" caseSensitive="false">2019-06-13T03:59:59</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Class" npmPropertyId="1056" searchOperation="EQUALS" dataType="int32" caseSensitive="false">65</SimpleAttributeExpression>
     </ExpressionSet>
   </SearchCriteria>
 </QueryString>
</SearchJobConfig>
'

DECLARE @Job TABLE(xConfig XML)

INSERT @Job ( xConfig )
VALUES ( @x )

SELECT j.*
      , ca.c.value('text()[1]', 'VARCHAR(30)') AS date_val
FROM @Job AS j
CROSS APPLY j.xConfig.nodes('/SearchJobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression') AS ca(c)
WHERE ca.c.exist('@displayName[.= "Date"]') = 1;

要將所有內容放在一條線上，只需執行以下操作：

SELECT *
FROM 
(
   SELECT STUFF(
   (SELECT N' ' + ca.c.value('text()[1]', 'NVARCHAR(MAX)')
    FROM @Job AS j
    CROSS APPLY j.xConfig.nodes('/SearchJobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression') AS ca(c)
    WHERE ca.c.exist('@displayName[.= "Date"]') = 1
    FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'') 
) AS x(date_val);

如果您希望日期值在同一行但不同的列中，您可以使用 shrednodes()來獲取每個節點的一行ExpressionSet，然後使用屬性displayName和searchOptioninvalue()來獲取開始和結束日期。

declare @x xml = '
<SearchJobConfig>
 <QueryID>1072</QueryID>
 <QueryString>
   <SearchCriteria name="Search query" >
     <ExpressionSet logicalOperator="AND">
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="GREATER_EQUAL" dataType="string" caseSensitive="false">2019-06-01T04:00:00</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Date" npmPropertyId="4" searchOperation="LESS_EQUAL" dataType="string" caseSensitive="false">2019-06-13T03:59:59</SimpleAttributeExpression>
       <SimpleAttributeExpression displayName="Class" npmPropertyId="1056" searchOperation="EQUALS" dataType="int32" caseSensitive="false">65</SimpleAttributeExpression>
     </ExpressionSet>
   </SearchCriteria>
 </QueryString>
</SearchJobConfig>
';
declare @Job table(xConfig xml);
insert @Job (xConfig) values (@x);

select T.X.value('(SimpleAttributeExpression
                  [
                   @displayName = "Date" and 
                   @searchOperation = "GREATER_EQUAL"
                  ]/text())[1]', 'datetime') as date_val_start,
      T.X.value('(SimpleAttributeExpression
                  [
                   @displayName = "Date" and 
                   @searchOperation = "LESS_EQUAL"
                  ]/text())[1]', 'datetime') as date_val_end
from @Job as j
 cross apply j.xConfig.nodes('/SearchJobConfig/QueryString/
                               SearchCriteria/ExpressionSet') as T(X);

結果：

date_val_start            date_val_end
2019-06-01 04:00:00.000   2019-06-13 03:59:59.000

引用自：https://dba.stackexchange.com/questions/240586