question

don 2 avatar image
don 2 asked

How to right justify and numeric xml column?

The following statement yields error 6850. What is the correct syntax to right justify a numeric field? SET @xml = CAST(( SELECT [Name] AS 'td','', [Amount] AS 'td align=right' FROM #Temp ORDER BY Rank FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 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
xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
The problem is that you attempt to use 'td align=right' as a column name which then translates to a node name, and this is not possible because you cannot have characters like space, equal sign etc as a part of column name. A quick and dirty solution which works could be to select [Amount] as some dummy alias and then replace the dummy with what you need. For example, this will work for you assuming that you @xml variable is of nvarchar(max) data type and from the query in question it looks like it is nvarchar(max). Here is the sample which will work: set @xml = replace(replace(cast(( select [Name] as td, [Amount] as [td1] from #Temp for xml path('tr'), elements) as nvarchar(max)), '', ''), '', ''); <\!-- **Begin Edit** Actually, it is possible to do it a bit more intelligently, it only takes to preceed the selection of the amount with attribute specification and retaining the '' after the Name selection to prevent the Msg 6852 error message reading something like "Attribute-centric column 'td/@align' must not come after a non-attribute-centric sibling". In other words, this should do it: set @xml = cast(( select [Name] as td, '', 'right' as [td/@align], [Amount] as td from #Temp for xml path('tr'), elements) as nvarchar(max)); **End Edit** --> Oleg
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you so much; your solution worked perfectly. I appreciate the time and effort you put forth in providing such a clean/clear solution. Have a good day, Don
0 Likes 0 ·
Thank you for the enhanced solution. -Don
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.