HTML formatting only being partially applied using FOR XML PATH and sp_send_dbmail
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 />...
Formatting sql correctly tp produce expected xml output
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...
How to union xml path. Sql
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...
How to concatenate distinct columns of otherwise duplicate rows into one row without using FOR XML PATH('')?
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. ...
select with rank functions
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 (...
What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT)?
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...
How to Create a hierarchy with FOR XML on SQLServer
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 XML path conversion results error
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(*)...