Bsrgvty

I'm trying to generate a SQL query to get the "DATE" values out of the following SQL XML:

I've tried something like this but I don't think I'm understanding the concepts.

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

Here is the XML as text:

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

The expected output would be the Dates:

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

and what would it take to get the results on the same line.. example:

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

I'm using SQL Server 2012 Enterprise Edition.

Does this get you what you want?

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;

To get everything on one line, just do this:

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);

If you want the date values in the same row but different columns you can shred using nodes() to get one row per ExpressionSet node and then use attributes displayName and searchOption in value() to get start and end date.

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);

Result:

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

You say that you want to retrieve multiple values from the XML, but then instructing your XPath to return only the first (#1) of them:

/ SearchjobConfig / QueryString / SearchCriteria / ExpressionSet / SimpleAttributeExpression [ 1 ]
 ___/
 Returns indexed item #1 _/

Lose this indexing clause and you'll get both values back, in the order they appear within the input XML.

Further reading: https://www.w3schools.com/xml/xpath_syntax.asp

As far as getting them into a single line ... it's [almost certainly] possibly, but is it really worth the effort for only two of them? YMMV.