For XML Path, forxmlpath

For XML Path, forxmlpath
Table data created by using For XML Path
First, let's take a look at the initial usage of for xml path: select ClassID, Sex, Age, Name from # PersonInfo for xml path ('personinfo'). The usage can be seen from the Name, for xml !!!
It is usually used together by group. Example:

-- COUNT the select ClassID, COUNT (1) as 'number older than 22', (select Name + ', 'From # PersonInfo where ClassID = p. classID for xml path ('') as 'name set from # PersonInfo p where Sex = 'male' and Age> 22 group by ClassID order by ClassIDselect ClassID, Age, COUNT (1) as 'number older than 22', (select Name + ', 'From # PersonInfo where ClassID = p. classID and Age = p. age for xml path ('') as 'name set from # PersonInfo p where Sex = 'male' and Age> 22 group by ClassID, Age order by ClassIDselect ClassID, Age, COUNT (1) as 'number older than 22', (select Name + ', 'From # PersonInfo where ClassID = p. classID and Age = p. age for xml path ('') as 'name set from # PersonInfo p where Sex = 'male' group by ClassID, Age having Age> 22 order by ClassID

The result is as follows:
Having is not as useful as you think. It is useful only after group by and is an aggregate function. Otherwise, it can be directly written after where. Note: The comma at the end of the name set can be removed using the stuff function.