sql table from which I am trying to get results based on some criteria as:
From above table I want to create
xml which has to return result as:
Here is the query I have written for the
DECLARE @ResourceName VARCHAR(50) , @XMLResult XML = NULL , @xMSG XML = NULL SET @ResourceName = 'Property' SET @xmsg = '<Results ReplyCode="0" ReplyText="Operation successful"><Result></Result></Results>' SET @xMSG = ( SELECT 0 AS [@ReplyCode] , 'Operation Successfull' AS [@ReplyText] , @ResourceName AS ResourceName , ( SELECT * FROM ( SELECT SystemName , DBName , ShortName , ClassSortOrder , Pcode , TabNumber , ColumnNumber , RowNumber FROM ScreenOrder ) SearchFieldsList ORDER BY ClassSortOrder FOR XML PATH('Field'), TYPE ) FOR XML PATH('Results') ) SET @XMLResult = @xMSG SELECT @XMLResult
But this query not returning expected result. This query returning result as:
I trying to put all that Fields as class wise in xml. Where do I need to modify query?