Introduction
In Part II of my XML Workshop I had presented a few examples which demonstrated how to read values from XML variables. This article presents a few more examples that read values from XML columns.
There were a few questions in the discussion forums asking more detailed examples that show how to read values from XML columns. One of the questions asked in the forum recently was about reading values from the report server subscriptions table . In this article, I am trying to present a set of detailed examples that explain
how to read values from the subscriptions table of report server.
Sample Data
For the purpose of this example, I have created a report subscription. The report has to be emailed to me on a weekly schedule, every Monday, Tuesday, Wednesday and Thursday. Now it is time to play with the subscriptions table.
Let us first query the subscriptions table and see how the data looks like. Let us run the following query to retrieve the report server subscription details. I have created a subscription with the name 'Send e-mail to jacob@dotnetquest.com'. You should change the subscription name to your own subscription.
1 SELECT
2 [description],
3 ExtensionSettings,
4 MatchData
5 FROM Subscriptions
6 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
The result shows that the columns ExtensionSettings and MatchData contain
data in XML format. However the data type is not XML. They are NTEXT columns which contain text data which has an XML structure.
Before we can use the XML methods on the columns, we need to convert the values
to XML data type. The following query converts the column to XML.
1 SELECT
2 [description],
3 CAST(ExtensionSettings AS XML) ext,
4 CAST(MatchData AS XML) AS match
5 FROM Subscriptions
6 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
Note that we have the results as XML values.
Now let us start reading values from the XML columns. Let us first read values from the ExtensionSettings column. This column contains the information I have configured for the e-mail delivery.
Run the following query.
1 /*
2 XML column value:
3
4 <ParameterValues>
5 <ParameterValue>
6 <Name>TO</Name>
7 <Value>jacob@dotnetquest.com</Value>
8 </ParameterValue>
9 <ParameterValue>
10 <Name>CC</Name>
11 <Value>jacob@reliancesp.com</Value>
12 </ParameterValue>
13 <ParameterValue>
14 <Name>BCC</Name>
15 <Value>jacob@excellenceinfonet.com</Value>
16 </ParameterValue>
17 <ParameterValue>
18 <Name>ReplyTo</Name>
19 <Value>jacob@dotnetquest.com</Value>
20 </ParameterValue>
21 <ParameterValue>
22 <Name>IncludeReport</Name>
23 <Value>True</Value>
24 </ParameterValue>
25 <ParameterValue>
26 <Name>RenderFormat</Name>
27 <Value>MHTML</Value>
28 </ParameterValue>
29 <ParameterValue>
30 <Name>Subject</Name>
31 <Value>@ReportName was executed at @ExecutionTime</Value>
32 </ParameterValue>
33 <ParameterValue>
34 <Name>Comment</Name>
35 <Value>Ah..this is a comment.</Value>
36 </ParameterValue>
37 <ParameterValue>
38 <Name>IncludeLink</Name>
39 <Value>True</Value>
40 </ParameterValue>
41 <ParameterValue>
42 <Name>Priority</Name>
43 <Value>NORMAL</Value>
44 </ParameterValue>
45 </ParameterValues>
46 */
47
48 SELECT
49 x.ext.value('Name[1]', 'varchar(20)') as [Name],
50 x.ext.value('Value[1]', 'varchar(30)') as [Value]
51 FROM
52 (
53 SELECT
54 [description],
55 CAST(ExtensionSettings AS XML) ext
56 FROM Subscriptions
57 ) AS P
58 CROSS APPLY ext.nodes('//ParameterValues/ParameterValue') x(ext)
59 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
60
61 /*
62 OUTPUT:
63
64 Name Value
65 -------------------- ------------------------------
66 TO jacob@dotnetquest.com
67 CC jacob@reliancesp.com
68 BCC jacob@excellenceinfonet.com
69 ReplyTo jacob@dotnetquest.com
70 IncludeReport True
71 RenderFormat MHTML
72 Subject @ReportName was executed at @E
73 Comment Ah..this is a comment.
74 IncludeLink True
75 Priority NORMAL
76 */
Now let us read the values from the MatchData column. This column contains the
information about the scheduling I have configured. In this example, I have scheduled
an e-mail delivery on Monday, Tuesday and Wednesday. Run the following query.
1 /*
2 <ScheduleDefinition>
3 <StartDateTime>2007-06-20T08:00:00.000+05:30</StartDateTime>
4 <EndDate>2007-07-12</EndDate>
5 <WeeklyRecurrence>
6 <WeeksInterval>1</WeeksInterval>
7 <DaysOfWeek>
8 <Monday>true</Monday>
9 <Tuesday>true</Tuesday>
10 <Wednesday>true</Wednesday>
11 <Thursday>true</Thursday>
12 </DaysOfWeek>
13 </WeeklyRecurrence>
14 </ScheduleDefinition>
15 */
16 SELECT
17 match.value('(//ScheduleDefinition/StartDateTime)[1]','varchar(30)') AS StartDateTime,
18 match.value('(//ScheduleDefinition/EndDate)[1]','varchar(10)') AS EndDate,
19 match.value('(//ScheduleDefinition/WeeklyRecurrence/WeeksInterval)[1]','varchar(10)') AS WeeksInterval,
20 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Sunday)[1]','varchar(5)') AS Sunday,
21 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Monday)[1]','varchar(5)') AS Monday,
22 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Tuesday)[1]','varchar(5)') AS Tuesday,
23 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Wednesday)[1]','varchar(5)') AS Wednesday,
24 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Thursday)[1]','varchar(5)') AS Thursday,
25 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Friday)[1]','varchar(5)') AS Friday,
26 match.value('(//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek/Saturday)[1]','varchar(5)') AS Saturday
27 FROM
28 (
29 SELECT
30 [description],
31 CAST(MatchData AS XML) AS match
32 FROM Subscriptions
33 ) AS P
34 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
35
36 /*
37 OUTPUT:
38
39 StartDateTime EndDate WeeksInterval Sunday Monday Tuesday Wednesday Thursday Friday Saturday
40 ------------------------------ ---------- ------------- ------ ------ ------- --------- -------- ------ --------
41 2007-06-20T08:00:00.000+05:30 2007-07-12 1 NULL true true true true NULL NULL
42 */
Note that we are passing the complete path to the required node in the value method. Let us make the syntax simpler by using the nodes method. The nodes method returns a collection of XML nodes from which you can retrieve the desired values. The XPath expression in the value method should be relative to the path of the nodes retrieved by the collection. Here is a simpler query which gives the same result.
1 SELECT
2 x.m.value('StartDateTime[1]','varchar(30)') AS StartDateTime,
3 x.m.value('EndDate[1]','varchar(10)') AS EndDate,
4 x.m.value('(WeeklyRecurrence/WeeksInterval)[1]','varchar(10)') AS WeeksInterval,
5 x.m.value('(WeeklyRecurrence/DaysOfWeek/Sunday)[1]','varchar(5)') AS Sunday,
6 x.m.value('(WeeklyRecurrence/DaysOfWeek/Monday)[1]','varchar(5)') AS Monday,
7 x.m.value('(WeeklyRecurrence/DaysOfWeek/Tuesday)[1]','varchar(5)') AS Tuesday,
8 x.m.value('(WeeklyRecurrence/DaysOfWeek/Wednesday)[1]','varchar(5)') AS Wednesday,
9 x.m.value('(WeeklyRecurrence/DaysOfWeek/Thursday)[1]','varchar(5)') AS Thursday,
10 x.m.value('(WeeklyRecurrence/DaysOfWeek/Friday)[1]','varchar(5)') AS Friday,
11 x.m.value('(WeeklyRecurrence/DaysOfWeek/Saturday)[1]','varchar(5)') AS Saturday
12 FROM
13 (
14 SELECT
15 [description],
16 CAST(MatchData AS XML) AS match
17 FROM Subscriptions
18 ) AS P
19 CROSS APPLY match.nodes('//ScheduleDefinition') x(m)
20 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
XPath is really fun. Let us make the query even simpler. Let us add one more level to the nodes(), '//ScheduleDefinition/WeeklyRecurrence'. With this change, all the XPath expressions given in the value() method should be relative to the location of the path given in the nodes() method. Note the first fields, which uses "..\" to point to a value in the parent node. Run the following query.
1 SELECT
2 x.m.value('../StartDateTime[1]','varchar(30)') AS StartDateTime,
3 x.m.value('../EndDate[1]','varchar(10)') AS EndDate,
4 x.m.value('(WeeksInterval)[1]','varchar(10)') AS WeeksInterval,
5 x.m.value('(DaysOfWeek/Sunday)[1]','varchar(5)') AS Sunday,
6 x.m.value('(DaysOfWeek/Monday)[1]','varchar(5)') AS Monday,
7 x.m.value('(DaysOfWeek/Tuesday)[1]','varchar(5)') AS Tuesday,
8 x.m.value('(DaysOfWeek/Wednesday)[1]','varchar(5)') AS Wednesday,
9 x.m.value('(DaysOfWeek/Thursday)[1]','varchar(5)') AS Thursday,
10 x.m.value('(DaysOfWeek/Friday)[1]','varchar(5)') AS Friday,
11 x.m.value('(DaysOfWeek/Saturday)[1]','varchar(5)') AS Saturday
12
13 FROM
14 (
15 SELECT
16 [description],
17 CAST(MatchData AS XML) AS match
18 FROM Subscriptions
19 ) AS P
20 CROSS APPLY match.nodes('//ScheduleDefinition/WeeklyRecurrence') x(m)
21 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
I have one more version of the query. This version uses a longer XPath expression in the nodes() method and uses a relative path
in the value() method.
1 SELECT
2 x.m.value('../../StartDateTime[1]','varchar(30)') AS StartDateTime,
3 x.m.value('../../EndDate[1]','varchar(10)') AS EndDate,
4 x.m.value('../WeeksInterval[1]','varchar(10)') AS WeeksInterval,
5 x.m.value('Sunday[1]','varchar(5)') AS Sunday,
6 x.m.value('Monday[1]','varchar(5)') AS Monday,
7 x.m.value('Tuesday[1]','varchar(5)') AS Tuesday,
8 x.m.value('Wednesday[1]','varchar(5)') AS Wednesday,
9 x.m.value('Thursday[1]','varchar(5)') AS Thursday,
10 x.m.value('Friday[1]','varchar(5)') AS Friday,
11 x.m.value('Saturday[1]','varchar(5)') AS Saturday
12 FROM
13 (
14 SELECT
15 [description],
16 CAST(MatchData AS XML) AS match
17 FROM Subscriptions
18 ) AS P
19 CROSS APPLY match.nodes('//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek') x(m)
20 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
Now let us combine the two queries. Here is the query which retrieves the values of the two columns we saw in the above examples.
1 SELECT
2 x.ext.value('Name[1]',
'varchar(20)') as [Name],
3 x.ext.value('Value[1]', 'varchar(30)') as [Value],
4 LEFT(y.m.value('../../StartDateTime[1]','varchar(30)'),10) AS StartDate,
5 y.m.value('../../EndDate[1]','varchar(10)') AS EndDate,
6 y.m.value('../WeeksInterval[1]','varchar(1)') AS WI,
7 y.m.value('Sunday[1]','varchar(5)') AS Sun,
8 y.m.value('Monday[1]','varchar(5)') AS Mon,
9 y.m.value('Tuesday[1]','varchar(5)') AS Tue,
10 y.m.value('Wednesday[1]','varchar(5)') AS Wed,
11 y.m.value('Thursday[1]','varchar(5)') AS Thu,
12 y.m.value('Friday[1]','varchar(5)') AS Fri,
13 y.m.value('Saturday[1]','varchar(5)') AS Sat
14 FROM
15 (
16 SELECT
17 [description],
18 CAST(ExtensionSettings AS XML) ext,
19 CAST(MatchData AS XML) AS match
20 FROM Subscriptions
21 ) AS P
22 CROSS APPLY match.nodes('//ScheduleDefinition/WeeklyRecurrence/DaysOfWeek') y(m)
23 CROSS APPLY ext.nodes('//ParameterValues/ParameterValue') x(ext)
24 WHERE description = 'Send e-mail to jacob@dotnetquest.com'
25
26 /*
27 OUTPUT:
28
29 Name Value StartDate EndDate WI Sun Mon Tue Wed Thu Fri Sat
30 -------------------- ------------------------------ ---------- ---------- ---- ----- ----- ----- ----- ----- ----- -----
31 TO jacob@dotnetquest.com 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
32 CC jacob@reliancesp.com 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
33 BCC jacob@excellenceinfonet.com 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
34 ReplyTo jacob@dotnetquest.com 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
35 IncludeReport True 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
36 RenderFormat MHTML 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
37 Subject @ReportName was executed at @E 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
38 Comment Ah..this is a comment. 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
39 IncludeLink True 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
40 Priority NORMAL 2007-06-20 2007-07-12 1 NULL true true true true NULL NULL
41 */
Conclusions
This installment of XML Workshop aims at explaining the usage of XQuery to retrieve values from an XML column. It demonstrates the usage of CROSS APPLY, the new keyword introduced by SQL Server 2005. It also gives a basic introduction to the usage of XPath expressions.
