A very, very good friend of mine runs a website where he posts quite a bit.
Others post on the same site.
Thankfully one of the persons that posted linked to his corporate website, as well.
A good thing as I found a few interesting posts on the guest author’s website.
Transact SQL supports both XML and JSON quite favorably.
XML makes combining multiple columns into a massable HTML offering can make things easier to pass around and digest.
Here is our play data…
Declare Table & Add Data
set nocount on go declare @tblCourse TABLE ( [id] int not null identity(1,1) , [courseID] varchar(20) not null , [name] varchar(60) not null ) insert into @tblCourse ( [courseID], [name] ) values ('CS 1110', 'Introduction to Computing Using Python') , ('CS 2800', 'Discrete Structures') , ('CS 4410', 'Operating Systems')
SELECT tblC.[courseID] , tblC.[name] FROM @tblCourse tblC order by tblC.[courseID]
Single Column – Column Name – System Assigned
SELECT td = tblC.[courseID], '' , td = tblC.[name], '' FROM @tblCourse tblC order by tblC.[courseID] FOR XML PATH('tr')
- The System assigns column name to columns generated via “FOR XML PATH“
Single Column – Column Name – User Specified
select xml = ( SELECT td = tblC.[courseID], '' , td = tblC.[name], '' FROM @tblCourse tblC order by tblC.[courseID] FOR XML PATH('tr') )
- Using as or equal we are able to override the system-assigned name
Dedicating to hard-working good men and women!