Selecting XML Value from field with xml string in where clause

Question

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

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Mar 11 '22 at 15:05
Your query doesn't give me that error: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b5523335f3d9759220deadf6e14826ef). That suggests that the column *isn't* called `recurrenceinfo`. Also you want the `value` method, not `query`. `Start` is also an attribute of `RecurrenceInfo` *not* an element. — Thom A, Mar 11 '22 at 15:06
@Larnu, fyi, the field recurrenceinfo in the table is of type string. I tried value as well but get same error. — dk96m, Mar 11 '22 at 15:17
You can't use `xml` methods on a string based data type, @dk96m . You'll need to `CONVERT` the data first; or better yet fix the design and store your XML data as `xml`. — Thom A, Mar 11 '22 at 15:20
ok, so i change the table so that field is of xml type. @Larnu — dk96m, Mar 11 '22 at 15:21
So now that I changed it to XML, I am a bit closer. I tried SELECT recurrenceinfo.value('(/RecurrenceInfo//Start/node())[1]', 'nvarchar(max)') as StartDt FROM bench_request but all i get back are null values. — dk96m, Mar 11 '22 at 15:29

score 1 · Answer 1 · answered Mar 11 '22 at 15:33

1

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

answered Mar 11 '22 at 15:33

dk96m

301
3
18

1

You can change the data type to `datetime` although it doesn't help that the format is not an unambiguous one (depends on language/region) – Charlieface Mar 11 '22 at 16:12

Yitzhak Khabinsky · Answer 2 · 2022-03-11T16:43:59.043

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;

score 0 · Answer 3 · answered Mar 11 '22 at 15:31

0

select SUBSTRING(recurrenceinfo,25,19) from bench_request

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

answered Mar 11 '22 at 15:31

Gennaro Lippiello

73
1
6

Selecting XML Value from field with xml string in where clause

3 Answers3