Something like a Nested FOR XML PATH

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Something like a Nested FOR XML PATH

Author

Topic

deadtrees
Starting Member

26 Posts

Posted - 2014-06-11 : 12:21:44

I'm designing a report. I want to concatenate two types of data (Category + Result) for a specific item and in the past have used this schema with FOR XML Path ('') to do so.

Item - Category1 + Result1, Category1 + Result2, Category2 + Result1, Category3 + Result1, etc

I'm looking to conserve space and make the report more easy to read and I want to put the data in this format in the last column:

Category 1
Result 1
Result 2
Category 2
Result 1
Category 3
Result 1
etc

The closest thing I've found is the nested for XML Path, but I actually don't want the XML tags, just the output in the format I described. Is there a way to do that?

Here's my code:

Select ER.Results as EVALUATION
	,(STUFF(ISNULL((SELECT CHAR(10)+  '' + ISNULL(CASE E2.IndTo
											WHEN 1
												THEN '#1 - '
											WHEN 2
											    THEN '#2 - '
											WHEN 3
												THEN '#3 - '
											WHEN 4
											    THEN '#4 - '
											WHEN 5
												THEN '#5 - '
											WHEN 6
											    THEN '#6 - '											    
											WHEN 7
												THEN '#7 - '
											WHEN 8
											    THEN '#8 - '
											WHEN 9
												THEN '#9 - '
											WHEN 10
											    THEN '#10 - '											    
											WHEN 12
												THEN 'L.Palm - '
											WHEN 13
											    THEN 'R.Palm - '
											WHEN 14
												THEN 'L.Foot - '
											WHEN 15
											    THEN 'R.Foot - '											    
										END ,'') + '' + Upper(S.SubjectLastName +', '+S.SubjectFirstName)
					FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2
					INNER 
						JOIN LIMS_DATA_Production.dbo.Subjects S
							ON E2.SubjectID = S.ID
					WHERE S.CaseID = 3820 AND S.SDelete = 0 AND E2.Evaluation = ER.ID
					FOR XML PATH ('') ),' NO RESULTS'),1,1,'')) AS PERSON

From EvaluationResults ER
FOR XML Path ('')

deadtrees
Starting Member

26 Posts

Posted - 2014-06-12 : 10:18:11

I found the answer. add this after each FOR XML PATH ('')

(
select ...
from t
for xml path(''), type
).value('.', 'nvarchar(max)')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources