xml - SSRS query using WITH XMLNAMESPACES not returning dataset -

the sql query have written ssrs report executes in ssms fails return result when run in ssrs report. there no error on report server or on database server. ssrs report renders contains no data. tried running sql profiler , query not show @ all. have several other reports on server run correctly , query appears in sql profiler. thing different in broken report querying xml data using ;with xmlnamespaces(default 'urn:hl7-org:v3')

i same result running against sql server 2005 , 2008

here query, ideas on why nothing returned when run in ssrs. have modified query remove/modified client info may cause syntax error, unmodified query runs , returns results when run in ssms

declare @mindate datetime; declare @maxdate datetime; declare @foodata table (msgid varchar(100), transactionname     varchar(30),sourcesystem varchar(12),msgday date)   ;with xmlnamespaces(default 'urn:hl7-org:v3')  insert @foodata select         msglogid msgid       ,transactionname =             case left(msgbody,28)                     --when '<qqqresult' 'qqqresult'                     when '<qqqresult xmlns=' 'qqqresult'                     else 'qqqqrevised'                     end     , sourcesystem =             case left(msgbody,21)                     --when '<qqqresult' convert(xml,[msgbody]).value('(/qqqresult/receiver/device/id/@extension)[1]','varchar(1000)')                     when '<qqqresult' convert(xml,[msgbody]).value('(/qqqresult/sender/device/id/@extension)[1]','varchar(1000)')                     else convert(xml,[msgbody]).value('(/qqqrevised/sender/device/id/@extension)[1]','varchar(1000)')                     end      , convert(date,[msgdatetime]) msgday    [foo].[dbo].[foomegerperson_wcf]     [transactionstatus] = 'valid'  select @maxdate=dateadd(day,0,datediff(day,0, max([msgdatetime]))) ,@mindate=dateadd(day,0,datediff(day,0, min ([msgdatetime])) )from [foo].[dbo].[foomegerperson_wcf]  -- declare temp table query msg type can create join declare @msgnametable table (msgname varchar(30)) insert @msgnametable (msgname) values ('qqqresult'),('foorevised')  declare @sourcetable table (sourcename varchar(10)) insert @sourcetable (sourcename) values ('foo_1'),('foo_2')  --select * @msgnametable -- create date table containing every day between start , end declare @datetable table (d date)  insert @datetable select     top (datediff(day, @mindate, @maxdate) + 1) date = dateadd(day, row_number() on (order a.object_id) - 1,  @mindate)         sys.all_objects cross join                       sys.all_objects b  -- cross join create daily entry every msg type , every source system                       declare @datenamesourcetable  table (d date, msgname varchar (30),sourcename varchar(10))  insert @datenamesourcetable                                          select dt.d, mn.msgname, st.sourcename @datetable dt cross join @msgnametable mn cross join @sourcetable st --select * @datenamesourcetable    select count(x.msgid) messagecount,dsn.msgname transactionname ,dsn.d, dsn.sourcename  @foodata   x   right join @datenamesourcetable dsn on x.msgday = dsn.d  , x.sourcesystem = dsn.sourcename  , x.transactionname = dsn.msgname    group dsn.d, dsn.msgname, dsn.sourcename   order dsn.d, dsn.sourcename , dsn.msgname 

the thing different in broken report querying xml data using ;with xmlnamespaces(default 'urn:hl7-org:v3')

well, quite important only thing...

declare @xml xml= '<root><a>test</a></root>';  select @xml.value('(/root/a)[1]','nvarchar(max)') 

returns "test" expected

with xmlnamespaces(default 'tmpuri') select @xml.value('(/root/a)[1]','nvarchar(max)') 

returns "null"

while

declare @xml xml= '<root xmlns="tmpuri"><a>test</a></root>';  xmlnamespaces(default 'tmpuri') select @xml.value('(/root/a)[1]','nvarchar(max)') 

returns "test" same without namespace hint return "null"...

there 1 general trick omit namespaces: use wildcard!

select @xml.value('(/*:root/*:a)[1]','nvarchar(max)')