xQuery problems on subscriptions.extensionSettings column
in [Reports]
|
From: kent.eilers on 2 Jul 2008 15:02 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.
From: Bruce L-C [MVP] on 2 Jul 2008 15:48 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. -- Bruce Loehle-Conger MVP SQL Server Reporting Services <kent.eilers(a)res-direct.com> wrote in message news:670d1c47-47e3-4940-a3f8-4971b5bdcf98(a)j22g2000hsf.googlegroups.com... > 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.
From: kent.eilers on 3 Jul 2008 12:57 On Jul 2, 2:48 pm, "Bruce L-C [MVP]" <bruce_lcNOS...(a)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 > 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. > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > <kent.eil...(a)res-direct.com> wrote in message > > news:670d1c47-47e3-4940-a3f8-4971b5bdcf98(a)j22g2000hsf.googlegroups.com... > > > 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 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.
From: Bruce L-C [MVP] on 3 Jul 2008 15:47 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 <kent.eilers(a)res-direct.com> wrote in message news:198efc3f-c2b4-4a9e-b8c3-b9c3020d0a55(a)79g2000hsk.googlegroups.com... On Jul 2, 2:48 pm, "Bruce L-C [MVP]" <bruce_lcNOS...(a)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 > 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. > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > <kent.eil...(a)res-direct.com> wrote in message > > news:670d1c47-47e3-4940-a3f8-4971b5bdcf98(a)j22g2000hsf.googlegroups.com... > > > 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 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.
From: kent.eilers on 7 Jul 2008 16:00 On Jul 3, 2:47 pm, "Bruce L-C [MVP]" <bruce_lcNOS...(a)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 > 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 > > <kent.eil...(a)res-direct.com> wrote in message > > news:198efc3f-c2b4-4a9e-b8c3-b9c3020d0a55(a)79g2000hsk.googlegroups.com... > On Jul 2, 2:48 pm, "Bruce L-C [MVP]" <bruce_lcNOS...(a)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 > > 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. > > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > > <kent.eil...(a)res-direct.com> wrote in message > > >news:670d1c47-47e3-4940-a3f8-4971b5bdcf98(a)j22g2000hsf.googlegroups.com.... > > > > 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 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. 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/thread/e18a5070e5401c17/46ef3f12137c67d3?hl=en&lnk=st&q=#46ef3f12137c67d3
|
Pages: 1 Prev: Email Options in Reporting Services Next: Change Start Page of Report in Code |