How to create xml from sql table?

Question

I have sql table from which I am trying to get results based on some criteria as:

enter image description here

From above table I want to create xml which has to return result as:

enter image description here

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:

enter image description here

I trying to put all that Fields as class wise in xml. Where do I need to modify query?

Answer 1

Use FOR XML PATH with subqueries to get the appropriate level of nesting, something like this:

DECLARE @yourTable TABLE
 (
 ResourceName VARCHAR(20),
 ClassName VARCHAR(20),
 SystemName VARCHAR(20),
 ShortName VARCHAR(20),
 ClassSortOrder INT,
 TabNumber INT,
 RowNumber INT,
 DBName VARCHAR(20)
 )
INSERT INTO @yourTable VALUES
 ( 'Property', 'Commercial', 'Type', 'Type', 1, 1, 1, 'Fields Type' ),
 ( 'Property', 'Residential', 'Address', 'Address', 2, 2, 2, 'Fields Address' ),
 ( 'Property', 'MultiFamily', 'Price', 'Price', 3, 3, 3, 'Fields Price' ),
 ( 'Property', 'LotsAndLandy', 'BuildYear', 'BuildYear', 4, 4, 4, 'Fields Type' )
SELECT * FROM @yourTable
SELECT 
 0 AS "@ReplyCode",
 'Operation Successful' AS "@ReplyText",
 (
 SELECT
 ResourceName AS "ResourceName",
 (
 SELECT 
 ClassName AS "ClassName",
 (
 SELECT
 SystemName AS "SystemName",
 DBName AS "DBName",
 ShortName AS "ShortName",
 ClassSortOrder AS "ClassSortOrder"
 FOR XML PATH(''), TYPE
 ) AS "Field"
 FROM @yourTable c
 WHERE r.ResourceName = c.ResourceName
 FOR XML PATH('Class'), TYPE
 ) AS "Classes"
 FROM ( SELECT DISTINCT ResourceName FROM @yourTable ) r
 FOR XML PATH(''), TYPE
 ) AS "*"
FOR XML PATH('Results'), TYPE

It's not 100% clear as your sample data is different from your sample XML but hopefully that gives you a starting point.