x

How can I right justify a numeric field in an xml generated data set?

Hello, The example below works fine if I change 'td align=right' to 'td align’, of course then [Ranking Points] is left justified. So my question is, how can I right justify [Ranking Points]? I tried surrounding the word 'right' with parenthesis (“”) and quote/quote (‘’) without success. As is the sql below yields:

 Msg 6850, Level 16, State 1, Line 16
 Column name 'td align=right' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

Thank you in advance for your help. Regards, DonF

 CREATE TABLE #Temp ( 
   [Rank]  [int],
   [Player Name]  [varchar](128),
   [Ranking Points] [int],
   [Country]  [varchar](128))
 INSERT INTO #Temp
 SELECT 1,'Rafael Nadal',12390,'Spain'
 UNION ALL
 SELECT 2,'Roger Federer',7965,'Switzerland'
 UNION ALL
 SELECT 3,'Novak Djokovic',7880,'Serbia'
 
 DECLARE @xml NVARCHAR(MAX)
 DECLARE @body NVARCHAR(MAX)
 
 SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
        [Ranking Points] AS 'td align=right','', Country AS 'td'
 FROM  #Temp ORDER BY Rank 
 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
 
 SET @body ='<html><body><H3>Tennis Rankings Info</H3>
 <table border = 1> 
 <tr>
 <th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    
 
 
 SET @body = @body + @xml +'</table></body></html>'
 
 --EXEC msdb.dbo.sp_send_dbmail
 --@body = @body,
 --@body_format ='HTML',
 --@recipients = 'myemail@company.org', -- replace with your email address
 --@subject = 'E-mail in Tabular Format' ;
 
 DROP TABLE #Temp
more ▼

asked Jul 11, 2011 at 09:35 AM in Default

avatar image

don 2
106 9 9 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It's just a matter of popping the one with an attribute inside a subquery:

 SET @xml = CAST((
     SELECT [Rank] as [td],'',
            [Player Name] as [td],
            (SELECT 'right' AS '@align',[Ranking Points] AS 'data()' for XML path('td'),type),
            Country as [td]
     FROM  #Temp ORDER BY Rank 
     FOR XML PATH('tr'), ELEMENTS 
     ) AS NVARCHAR(MAX))
more ▼

answered Jul 11, 2011 at 04:46 PM

avatar image

Mister Magoo
2.1k 2 5 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x208

asked: Jul 11, 2011 at 09:35 AM

Seen: 2195 times

Last Updated: Jul 11, 2011 at 02:47 PM

Copyright 2018 Redgate Software. Privacy Policy