 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Reporting Services / July 2008xQuery problems on subscriptions.extensionSettings column
I'm trying to return the email addresses for subscriptions in SSRS. I figured i'd use the native xml abilities of SQL-Server2005..i.e. xQuery. but I can't get the syntax figured out.... select extensionSettings.query('/Name') from Subscriptions .....yeilds error: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "extensionSettings" or the user-defined function or aggregate "extensionSettings.query", or the name is ambiguous. as a matter of fact...whatever i try i get this error message... I am running the db at compatability leve 90..so that is not the problem.... this column is xQuery 'queriable' correct? could someone provide an example syntax to pull the name values out? thanks. The data type is not XML for that field. I am not familiar with using xquery. I could get the xquery you used below to work but it returned empty fields. select cast(extensionSettings as xml).query('/Value') from Subscriptions If you get this to work please post what you did back here. It would be interesting for me to see the result.  SignatureBruce Loehle-Conger MVP SQL Server Reporting Services > I'm trying to return the email addresses for subscriptions in SSRS. > [quoted text clipped - 21 lines] > > thanks. On Jul 2, 2:48 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com> wrote: > The data type is not XML for that field. I am not familiar with using > xquery. I could get the xquery you used below to work but it returned empty [quoted text clipped - 38 lines] > > > thanks. the following works: SELECT SubscriptionID, extensionSettings, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]', 'varchar(max)') AS col1, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]', 'varchar(max)') AS col2, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]', 'varchar(max)') AS col3, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[4]', 'varchar(max)') AS col4, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[5]', 'varchar(max)') AS col5 from Subscriptions but is not ideal.... what i think i need to do is use xQuery but do not understand it well enough to accomplish my goals... I can't seem to pull the actual values out: SELECT CAST(extensionSettings AS XML).query (' for $v_ in (//ParameterValue) where $v_/Name = To return <ParameterValue> { (<Name></Name> , <Value></Value> ) } </ParameterValue> ') from Subscriptions .....yields blank rows. I've been wanting to create a report about my subscriptions. The below takes what you did and adds additional information. This is undocumented so take what I did with a grain of salt. I observed that recurrencetype of a 1 meant it was a one shot subscription. SELECT b.name, b.path,CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]', 'varchar(max)') AS Email, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]', 'varchar(max)') AS Format, a.lastruntime,enddate,laststatus, recurrencetype from Subscriptions a inner join catalog b on a.report_OID = b.ItemID inner join reportschedule c on a.subscriptionid= c.subscriptionid inner join schedule d on c.scheduleid = d.scheduleid where recurrencetype != 1 and (enddate > getdate() or enddate is null) order by path, name Bruce Loehle-Conger MVP SQL Server Reporting Services On Jul 2, 2:48 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com> wrote: > The data type is not XML for that field. I am not familiar with using > xquery. I could get the xquery you used below to work but it returned [quoted text clipped - 39 lines] > > > thanks. the following works: SELECT SubscriptionID, extensionSettings, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]', 'varchar(max)') AS col1, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]', 'varchar(max)') AS col2, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]', 'varchar(max)') AS col3, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[4]', 'varchar(max)') AS col4, CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[5]', 'varchar(max)') AS col5 from Subscriptions but is not ideal.... what i think i need to do is use xQuery but do not understand it well enough to accomplish my goals... I can't seem to pull the actual values out: SELECT CAST(extensionSettings AS XML).query (' for $v_ in (//ParameterValue) where $v_/Name = To return <ParameterValue> { (<Name></Name> , <Value></Value> ) } </ParameterValue> ') from Subscriptions .....yields blank rows. On Jul 3, 2:47 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com> wrote: > I've been wanting to create a report about my subscriptions. The below takes > what you did and adds additional information. This is undocumented so take [quoted text clipped - 107 lines] > > .....yields blank rows. thanks!... Bob gave some useful examples as well on another post i did to the xml group: http://groups.google.com/group/microsoft.public.sqlserver.xml/browse_thread/thre ad/e18a5070e5401c17/46ef3f12137c67d3?hl=en&lnk=st&q=#46ef3f12137c67d3 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.