Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop V - Reading Values from XML Columns

By Jacob Sebastian,

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.

Total article views: 9384 | Views in the last 30 days: 18
 
Related Articles
FORUM

varchar issue

varchar issue

FORUM

Tricky ...VARCHAR

VARCHAR logics

FORUM

Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)

FORUM

converting varchar to numeric

converting varchar to numeric

FORUM

VARCHAR(x) VS VARCHAR(MAX)

any downside to VARCHAR(MAX)?

Tags
basics    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones