score:3
Accepted answer
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
)
More questions
- SQL - Correctly format CSS class using XML PATH
- I want to read XML from drive path using SQL Query in table format
- SQL Server: how to remove last comma after combining rows using XML Path
- SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH
- SQL Error using using XML Path Method, Error converting data type varchar to float
- How to Convert Table Data into xml format using sql with multiple sub nodes
- SQL Server T-SQL Get Data into XML using FOR XML PATH
- Using XML PATH command in SQL Server to combine multiple values into one cell
- How to remove leading space using FOR XML PATH in SQL Server
- Using XML PATH in Sql Stored Procedure
- How to use XML Auto to get the format obtained by XML Path in sql server
- SQL for XML Path using data values for node names and nesting
- Formating color in SQL Server using FOR XML PATH
- SQL - Query using XML Path
- sql server Concatenate Multiple Rows Using FOR XML PATH
- How to correctly format For XML path results
- Repeat first element XML SQL Server using for path
- Issue converting special characters from SQL to XML using XML PATH
- SQL SERVER FOR XML PATH - Exporting data to excel using Integration Services
- Generate XML path format from SQL table
- How to export data as CSV format from SQL Server using sqlcmd?
- How to convert records in a table to xml format using T-SQL?
- SQL Server : FOR XML PATH - nesting / grouping
- selecting individual xml node using SQL
- Why do I have to SET ARITHABORT ON when using xml in sql server 2005?
- Using UNION ALL in STUFF / XML Path
- SQL Server for xml path add attributes and values
- Extra Characters while using XML PATH
- Where clause when using XML in SQL
- How do I use column values as xml element names using for xml in SQL Server 2005?
More questions with similar tag
- Does an index already cover a clustered primary key?
- Stored Procedure float input param truncating value
- How to sum values based on a second table, and group them by a third?
- Using STUFF in SQL with a condition
- Cross Apply vs "comma Join"
- How to insert one row from Table A to Table B But not Primay key
- Get jobs count without using group by
- MS SQL Server: transforming SCD-2 to SCD-2 with less variables
- Subselect Query Improvement
- IIS: Connecting to .mdf database - web config problem
- SQL to find only those records which are before certain date
- SQLServer connection with PowerShell Issue setting ConnectionTimeout Property
- How to debug stored procedures in sql server 2005
- Manually disposing SqlDbConnection causes "internal .net framework provider error 1"
- How to put a database in the project? C#
- Update table based on join multiple rows SQL Server
- Convert YYYYMMDD to DATE
- How to select rows based on two columns creating an identifier and the max date
- System.Data.SqlClient.SqlException(0x80131904) : Incorrect syntax near '.'
- t-sql calling function in stored procedure assistance
- Shredding XML with unknown schema
- Quickly changing SSIS-packages data source parameters for easy migration
- How to calculate a count of users who did a thing X times during a rolling 7-day period in SQL Server?
- Use CONSTRAINT keyword when creating a table
- SQL Server schema-owner permissions
- Delete spaces and special characters
- SQL Server SMO: drop multiple objects with dependencies
- SQL Server 2005 replication triggers - how to?
- Export a SQL Server database from C# and re-import
- Powershell restore SQL Server database to new database
Source:
stackoverflow.com