/ Published in: SQL
To sort varchar fields which contain numeric data & returning XML
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
eg. QuestionId QuestionCode Question IsActive 1 5 ABC 1 2 AC5 ABC2 1 3 60 ABCD 1 4 A4 ABCF 1 5 1 ABCASD 1 SELECT ( SELECT QuestionId AS QuestionID, -- INT QuestionCode AS QuestionCode, -- VARCHAR Question AS Question, -- VARCHAR IsActive AS IsActiveValue -- BIT FROM Questions WHERE IsActive = 1 ORDER BY -- [To sort varchar fields] CASE ISNUMERIC(QuestionCode) WHEN 1 THEN Replicate(CHAR(35), 100 - LEN(QuestionCode)) + QuestionCode ELSE QuestionCode END FOR XML PATH('Question'), TYPE ) FOR XML PATH('BOQuestionList'), TYPE