Transact SQL – Select And “FOR XML PATH”

Background

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

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.

 

Data

Here is our play data…

Course ID Course Name
CS 1110 Introduction to Computing Using Python
CS 2800 Discrete Structures
CS 4410 Operating Systems

 

Code

Declare Table & Add Data

SQL

 


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')


 

Fetch Data

Simple Query

SQL


SELECT 
    
      tblC.[courseID]  

    , tblC.[name]  

FROM @tblCourse tblC

order by 
    tblC.[courseID]

 

Output

 

Single Column – Column Name – System Assigned

SQL


SELECT 
    
      td = tblC.[courseID], ''  

    , td = tblC.[name], ''  

FROM @tblCourse tblC

order by 
    tblC.[courseID]

FOR XML PATH('tr')   


 

Output

 

Explanation

  1. The System assigns column name to columns generated via “FOR XML PATH

 

Single Column – Column Name – User Specified

SQL


select

    xml =
    (
        SELECT 
              td = tblC.[courseID], ''  

            , td = tblC.[name], ''  

        FROM @tblCourse tblC

        order by 
            tblC.[courseID]
        
        FOR XML PATH('tr')   

    ) 




 

Output

Explanation

  1. Using as or equal we are able to override the system-assigned name

 

Dedicating

Dedicating to hard-working good men and women!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s