1

Apologies in advance but I have searched the net and this site for an answer but I just can't seem to find it. I've tried

I have a column called RecurrencePatternXml with xml in it as shown in the example below which has different data in it and can change frequently. 

<Recurrence StartDate="2015-03-10T00:00:00+00:00"><Weekly><Pattern Count="1"><Day>friday</Day></Pattern></Weekly></Recurrence>
<Recurrence StartDate="2015-04-01T00:00:00+01:00"><Weekly><Pattern Count="1"><Day>tuesday</Day><Day>thursday</Day><Day>sunday</Day></Pattern></Weekly></Recurrence>
<Recurrence StartDate="2015-04-27T00:00:00+01:00"><None /></Recurrence>    
<Recurrence StartDate="2015-03-10T00:00:00+00:00"><Daily FrequencyPerDay="2"><Pattern><DayPattern Value="1" /></Pattern></Daily></Recurrence>
<Recurrence StartDate="2015-10-02T00:00:00+01:00"><Weekly><Pattern Count="2"><Day>monday</Day><Day>wednesday</Day><Day>friday</Day><Day>sunday</Day></Pattern></Weekly></Recurrence>

I'm trying to write a query that will show all of the individual data in individual columns and be null where there is no data.

Using the data below

<Recurrence StartDate="2015-11-12T00:00:00+00:00"><Weekly FrequencyPerDay="2"><Pattern count="1"><Day>tuesday</Day><Day>thursday</Day><Day>saturday</Day><Day>sunday</Day></Pattern></Weekly></Recurrence>
<Recurrence StartDate="2015-03-10T00:00:00+00:00"><Daily FrequencyPerDay="2"><Pattern><DayPattern Value="1" /></Pattern></Daily></Recurrence>

This would be the output I would expect.

Recurrence     Weekly        Daily      Pattern      Daypattern     Day1       Day2         Day3        Day 4          Day5       Day6             Day7
2015-11-12     2             NULL        1           NULL           NULL       tuesday      NULL        thursday       NULL       saturday         sunday
2015-03-10     NULL          2           NULL        1              NULL       NULL         NULL       NULL            NULL       NULL             NULL
Improve this question
1
  • Hi, I just edited my answer to get more out of your XML... Commented May 24, 2016 at 15:23

1 Answer 1

Reset to default
1

Try something like this

declare @dummy TABLE(ID INT, YourPattern XML);
INSERT INTO @dummy VALUES(1,
'<Recurrence StartDate="2015-03-10T00:00:00+00:00"><Weekly><Pattern Count="1"><Day>friday</Day></Pattern></Weekly></Recurrence>
<Recurrence StartDate="2015-04-01T00:00:00+01:00"><Weekly><Pattern Count="1"><Day>tuesday</Day><Day>thursday</Day><Day>sunday</Day></Pattern></Weekly></Recurrence>
<Recurrence StartDate="2015-04-27T00:00:00+01:00"><None /></Recurrence>    
<Recurrence StartDate="2015-03-10T00:00:00+00:00"><Daily FrequencyPerDay="2"><Pattern><DayPattern Value="1" /></Pattern></Daily></Recurrence>
<Recurrence StartDate="2015-10-02T00:00:00+01:00"><Weekly><Pattern Count="2"><Day>monday</Day><Day>wednesday</Day><Day>friday</Day><Day>sunday</Day></Pattern></Weekly></Recurrence>');

SELECT R.value('@StartDate','datetime') AS Recurrence
      ,CASE WHEN R.value('local-name(*[1])','varchar(max)')='Weekly' THEN R.value('(*/Pattern/@Count)[1]','int') END AS Weekly
      ,CASE WHEN R.value('local-name(*[1])','varchar(max)')='Daily'  THEN R.value('(*/@FrequencyPerDay)[1]','int') END AS Daily
      ,'don''t know what you need here' AS Pattern
      ,R.value('(*/Pattern/DayPattern/@Value)[1]','int') AS DayPattern
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='monday' THEN 1 ELSE 0 END AS Monday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='tuesday' THEN 1 ELSE 0 END AS Tuesday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='wednesday' THEN 1 ELSE 0 END AS Wednesday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='thursday' THEN 1 ELSE 0 END AS Thursday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='friday' THEN 1 ELSE 0 END AS Friday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='saturday' THEN 1 ELSE 0 END AS Saturday
      ,CASE WHEN R.value('(*/Pattern/Day/text())[1]','varchar(max)')='sunday' THEN 1 ELSE 0 END AS Sunday
FROM @dummy
CROSS APPLY YourPattern.nodes('/*') AS A(R)

The result

+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| Recurrence              | Weekly | Daily | Pattern                       | DayPattern | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| 2015-03-10 00:00:00.000 | 1      | NULL  | don't know what you need here | NULL       | 0      | 0       | 0         | 0        | 1      | 0        | 0      |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| 2015-03-31 23:00:00.000 | 1      | NULL  | don't know what you need here | NULL       | 0      | 1       | 0         | 0        | 0      | 0        | 0      |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| 2015-04-26 23:00:00.000 | NULL   | NULL  | don't know what you need here | NULL       | 0      | 0       | 0         | 0        | 0      | 0        | 0      |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| 2015-03-10 00:00:00.000 | NULL   | 2     | don't know what you need here | 1          | 0      | 0       | 0         | 0        | 0      | 0        | 0      |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
| 2015-10-01 23:00:00.000 | 2      | NULL  | don't know what you need here | NULL       | 1      | 0       | 0         | 0        | 0      | 0        | 0      |
+-------------------------+--------+-------+-------------------------------+------------+--------+---------+-----------+----------+--------+----------+--------+
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Hi Shnugo. Thank you for your reply. I'll give that a go now.
Hi again. Thank you for your help. The first row seems to work but I don't think the rest are correct. Not sure if I'm just reading it incorrectly. There's only five rows of data that get loaded into the table. But there is 10 rows in the output and some of the dates don't match or are missing. Also there's incorrect times in the output and I was just needing e.g. 2015-03-10. Really grateful for your help.
@Mally Please add the expected output (fitting to the given data) to your question, thx
Hi Shnugo. I've edited my question above as I wasn't sure how to add new info. I've searched through my table to show how the data can be different in the rows and have shown this above.
@Mally, please check my updated answer, If you like it please vote it up, if it solves your issue please mark it as accepted. Thx!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.