将具有不同值的xml列拆分为多列

Question

事先道歉，但我已经搜索了网络和这个网站的答案，但我只是似乎找不到它。我试过了

我有一个名为RecurrencePatternXml的列，其中包含xml，如下面的示例所示，其中包含不同的数据，并且可以频繁更改。

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

我正在尝试编写一个查询，该查询将显示各个列中的所有单独数据，如果没有数据，则为null。

使用以下数据

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

这就是我所期望的结果。

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

Answer 1

试试像这样的东西

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)

结果

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