I have 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?
Best Answer
Use
FOR XML PATH
with subqueries to get the appropriate level of nesting, something like this:It's not 100% clear as your sample data is different from your sample XML but hopefully that gives you a starting point.