2023-08-22

SQL Server - Select - For XML Path

for xml path(‘’)

appears to be a concatination mechanism, or a way to change the output to an XML block

select UserName 
from employees 
for xml path('')

Using SQL Server Management Studio, the above statement produces an xml dataset. Clicking
this link brought up a window containing a list:

AgxxAm
AxxxaL
total of 40 of these records
ZarxxR
ZieccE

Blocking the columnname

select ','+UserName 
from employees 
for xml path('')

Produces the output of 1 xml packet named ‘xml_f5e2b61…’ output would resemble above
,AgxxxxxAm,AnxxxL,ZarxxxraR,ZiexxxnE

Sql Select - Concatenation of one field from multiple records.

Suppose you wanted a list of employees who are 42 years old. You could have a SQL statement
similar to the following:

1
2
3

select empUserName 
from employees 
where age = 42

you might receive a list similar to the following

AgxxxxxAm
AnxxxL
ZarxxxraR
ZiexxxnE

But suppose you wanted the list represented as one field, with values comma separated. Then you could use a combination of the for xml path(‘’) operator (to perform concatenation services) and the Stuff function (to trim off leading comma).

select stuff(
	( select ','+empUserName 
		from employees 
		where cdEmpStatus in ('98') 
		and cdEmpType='CON'
		for xml path('')
	),1,1,'') x

This will give you an output similar to the following

AgxxxxxAm,AnxxxL,ZarxxxraR,ZiexxxnE