'Selecting XML Value from field with xml string in where clause [duplicate]

I have a query that I am trying to change to get a value from an xml string. This is what I have right now:

SELECT * FROM bench_request WHERE bench_request.start_date >= 'Mar 11 2022  8:59AM'

In my table I have a field called recurrenceinfo that contains an XML string. Here is an example of that field:

<RecurrenceInfo 
Start="03/08/2022 08:00:00" 
End="03/21/2022 08:00:00" 
WeekDays="62" 
Id="c46571d7-220f-4e75-ad7c-843e8868d63f" 
Range="1" 
FirstDayOfWeek="0" 
Version="2"/>

What I am trying to do is get the start date from the xml string to use in replace of bench_request.start_date. So something like:

SELECT * FROM bench_request WHERE "Start from XML string" >= 'Mar 11 2022  8:59AM'

Basically I am trying to get all rows where the recurrenceinfo(Start) >= 'Mar 11 2022 8:59AM'.

I have searched and searched and can't figure it out. I have tried:

SELECT  recurrenceinfo.query(N'/RecurrenceInfo/Start') AS [Start] FROM bench_request

But I get an error "Cannot find either column "recurrenceinfo" or the user-defined function or aggregate "recurrenceinfo.value", or the name is ambiguous."

How can I accomplish this? recurrenceinfo field is of type string, not xml. Also get the attribute start

sqlsql-servertsql


Solution 1:[1]

Please try the following solution.

It is important to use ISO 8601 standard for DATE AND TIME formats ISO 8601

Unfortunately, your XML values are not compliant with the standard. That's why there is some @Start attribute value massaging to make it ISO 8601 compliant.

SQL

-- DDL and sample data population, start
DECLARE @benchrequest TABLE (ID INT IDENTITY PRIMARY KEY, recurrenceinfo XML);
INSERT INTO @benchrequest (recurrenceinfo) VALUES 
('<RecurrenceInfo Start="03/08/2022 08:00:00" End="03/21/2022 08:00:00" WeekDays="62" Id="c46571d7-220f-4e75-ad7c-843e8868d63f" Range="1" FirstDayOfWeek="0" Version="2"/>'),
('<RecurrenceInfo Start="05/25/2022 08:00:00" End="03/21/2022 08:00:00" WeekDays="62" Id="c46571d7-220f-4e75-ad7c-843e8868d63f" Range="1" FirstDayOfWeek="0" Version="2"/>');
-- DDL and sample data population, end

DECLARE @var DATETIME = '2022-03-11T08:59:00';

SELECT *
FROM @benchrequest
WHERE recurrenceinfo.exist('/RecurrenceInfo[xs:dateTime(concat(substring(@Start,7,4),"-",
substring(@Start,1,2),"-",
substring(@Start,4,2),"T", substring(@Start,12,8))) ge xs:dateTime(sql:variable("@var"))]') = 1;

Solution 2:[2]

Got it, the key was changing the datatable field from varchar to XML, then the following worked:

SELECT recurrenceinfo.value('(/RecurrenceInfo/@Start)[1]', 'nvarchar(max)') as StartDt FROM bench_request

Solution 3:[3]

select SUBSTRING(recurrenceinfo,25,19) from bench_request

This will give you the "Start" as String. Then you must convert it to datetime field

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 dk96m
Solution 3 Gennaro Lippiello

Related Questions