SQL - Correctly format CSS class using XML PATH

Question

I'm trying to format an HTML table by adding some CSS classes from a SQL Query using XML PATH syntax.

I tried to apply the answer found on: Assign CSS class to HTML tags generated with SQL 'FOR XML' but I don't know what I'm doing wrong:

DECLARE @tableHTML  NVARCHAR(MAX) ; 
SET @tableHTML =   
N'<table border="0" cellpadding="4" style="font-size:12px; font-family:Arial">' +  
N'<tr>' + 
N'<th style="border-bottom:1px solid #7AC0DA">Activity Date</th>' + 
N'<th style="border-bottom:1px solid #7AC0DA">Lead</th>' + 
N'<th style="border-bottom:1px solid #7AC0DA">Lead By</th>' +  
N'<th style="border-bottom:1px solid #7AC0DA">Activity Title</th>' +  
N'<th style="border-bottom:1px solid #7AC0DA">Featured On</th>' + 
CAST ( (SELECT 'trclass' as [@class],
          (SELECT StartDate as [*], 'tdclass' as [@class] FOR XML PATH('td'), type),
          (SELECT LeadGroup as [*], 'tdclass' as [@class] FOR XML PATH('td'), type),
          (SELECT LeadBy as [*], 'tdclass' as [@class] FOR XML PATH('td'), type),
          (SELECT Title as [*], 'tdclass' as [@class] FOR XML PATH('td'), type),
          (SELECT Featured as [*], 'tdclass' as [@class] FOR XML PATH('td'), type)
        FROM @Activites ORDER BY [StartDate],[LeadGroup],[LeadBy]
        FOR XML PATH('tr') , TYPE   
) AS NVARCHAR(MAX) ) +  
N'</table>';

I would like to have an output like this:

<table>
    <tr>
        <th style="border-bottom:1px solid #7AC0DA">Activity Date</th>
        <th style="border-bottom:1px solid #7AC0DA">Lead</th>
        <th style="border-bottom:1px solid #7AC0DA">Lead By</th>
        <th style="border-bottom:1px solid #7AC0DA">Activity Title</th>
        <th style="border-bottom:1px solid #7AC0DA">Featured</th>
    <tr>
    <tr clas="trclass">
        <td clas="tdclass">2016-01-01</td>
        <td clas="tdclass">Marketing</td>
        <td clas="tdclass">John Smith</td>
        <td clas="tdclass">Project XYZ</td>
        <td clas="tdclass">Web - Magazine</td>
    </tr>
</table>

However, I'm getting the following error when executing the query:

Attribute-centric column '@style' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH

What am I doing wrong? I must say I'm a little confused on how XML PATH works. Thanks!

Bert · Accepted Answer · 2016-10-17 21:33:44Z

Your error indicates that somewhere you are selecting something you want to be an attribute (the error indicates 'style') after you are selecting something that is not an attribute (probably one of your table headers). FOR XML PATH doesn't allow that, you have to select the attributes first. I cannot, however, see where you are selecting @style in your code.

I've modified your query to select the XML in the format you desire below.

declare @style nvarchar(50) = 'border-bottom:1px solid #7AC0DA'
declare @Activities table
(
    StartDate datetime,
    LeadGroup nvarchar(10),
    LeadBy nvarchar(20),
    Title nvarchar(50),
    Featured nvarchar(50)
)
insert @Activities values
('1/1/2016', 'Marketing', 'John Smith', 'Project XYZ', 'Web - Magazine') 

select
    '0' as '@border',
    '4' as '@cellpadding',
    'font-size:12px; font-family:Arial' as '@style',
    (
        select  (select @style as '@style', 'Activity Date' as '*' for xml path('th'), type),
                (select @style as '@style', 'Lead' as '*' for xml path('th'), type),
                (select @style as '@style', 'Lead By' as '*' for xml path('th'), type),
                (select @style as '@style', 'Activity Title' as '*' for xml path('th'), type),
                (select @style as '@style', 'Featured' as '*' for xml path('th'), type)
        for xml path('tr'), type
    ),
    (
        select 'trclass' as '@class',
               (select StartDate as '*' for xml path('td'), type),
               (select LeadGroup as '*' for xml path('td'), type),
               (select LeadBy as '*' for xml path('td'), type),
               (select Title as '*' for xml path('td'), type),
               (select Featured as '*' for xml path('td'), type)
        from @Activities
        for xml path('tr'), type
    )
for xml path('table')

If you would like the results of that in an nvarchar(max) variable, just select it into it.

declare @tableHtml nvarchar(max)
select @tableHtml = (
    //query from above
)

Oh... this makes it so much easier to read and it makes sens to me now. Thanks a lot!

Collectives™ on Stack Overflow

SQL - Correctly format CSS class using XML PATH

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related