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:

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?

wBob · Accepted Answer · 2016-09-07 16:15:14Z

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.

Stack Exchange Network

How to create xml from sql table?

1 Answer 1

Your Answer

Hot Network Questions

How to create xml from sql table?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions