You can try using 2 XML variables and another FOR XML to combine them like so : declare @path2 XML = (select t1.Col1 ,t1.Col2 ,( select t2.Col1 ,t2.Col2 from #t2 t2 where t1.Col1 = t2.Col1 for xml path ('Path1'), root('RootPath1'),Type ) from #t1 t1 for xml path ('Path2')) declare @path22...
sql,sql-server-2008,group-by,common-table-expression,for-xml-path
You can use FOR XML PATH like this for concatenation ;with cte as ( select Data , SUBSTRING(Data,1,1) as Chars,1 as startpos from @t union all select Data, SUBSTRING(Data, startpos+1,1) as char,startpos+1 from cte where startpos+1<=LEN(data) ), CTE2 AS ( select Data,Chars,Cast(Chars as varchar(1)) + ' appears (' + cast(COUNT(*)...
sql,sql-server,concatenation,for-xml-path
Change the "for xml path('') )" section to "for xml path(''), root('root'), type).query('root').value ('.', 'varchar(max)')" this will unescape the characters correctly. Sorry for the poor formatting but not at my computer right now. I can give a full example later if you need. ...
sql-server,sql-server-2008,sp-send-dbmail,for-xml-path
You should use FOR XML PATH(''), type).value('.[1]','nvarchar(max)'). SET @accountList = STUFF(( SELECT ' <br /> ' + acct FROM @table FOR XML PATH(''), type).value('.[1]','nvarchar(max)'), 1, 1, '') This prints: <br /> 1234 <br /> 2345 <br /> 3456 <br /> 4567 While your query prints: <br /> 1234 <br />...
sql,linq,lambda,substring,for-xml-path
I'm assuming of course by Lambda expression you mean a Linq statement (e.g. to EF or Linq2Sql). The FOR XML PATH and STUFF example shown is a hack to workaround the lack of GROUP_CONCAT or LISTAGG in Sql Server. You don't need this at all in LINQ - simply load...
sql-server,sql-server-2008,for-xml-path
Do a correlated sub query in the column list against @tab2. select T1.name as 'PersonInfo/Name', T1.street as 'PersonInfo/Street', ( select T2.b as '*' from @tab2 as T2 where T2.a = T1.a for xml path('Type'), root('PersonSkill'), type ) from @tab1 as T1 where T1.name = 'Bruce' for xml path('Person') ...
sql-server,xml,for-xml,for-xml-path
Try this: DECLARE @Uname VARCHAR(15) = 'Dom', @Pword VARCHAR(15) = 'Monty'; SELECT RTRIM(@Uname) AS '@uname', RTRIM( @Pword) AS '@pword', (SELECT COALESCE(PortOfLanding,'') AS '@portOfLanding' FROM Landings.LandingHeaders WHERE Posted = 0 FOR XML PATH('Sale'), TYPE) FOR XML PATH('abc') You can go deeper like: SELECT RTRIM(@Uname) AS '@uname', RTRIM( @Pword) AS '@pword', (SELECT...
sql,sql-server,for-xml-path,dense-rank
Your question is a bit vague and it is extremely unclear what you want for output but this should get you started. if OBJECT_ID('tempdb..#t1') is not null drop table #t1 CREATE TABLE #t1 (NAME nvarchar(3),tbname NVARCHAR(2) ) INSERT INTO #t1 VALUES ('mp1','s1'), ('mp2','s1'), ('mp3','s1'), ('mp1','s2'), ('mp4','s1'); with tbnames as (...