主页 / dba / 问题 / 148995
Accepted
Kaishu
Asked: 2016-09-08 02:02:03 +0800 CST

如何从 sql 表创建 xml？

  • 772

我有一个sql表，我试图根据以下标准从中获得结果：

在此处输入图像描述

从上表中，我想创建xml必须返回结果为：

在此处输入图像描述

这是我为

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

但是这个查询没有返回预期的结果。此查询返回结果为：

在此处输入图像描述

我试图将所有这些字段作为类明智的放在 xml 中。我需要在哪里修改查询？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    与子查询一起使用FOR XML PATH以获得适当的嵌套级别，如下所示：

    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

    这不是 100% 清楚的，因为您的示例数据与您的示例 XML 不同，但希望这可以为您提供一个起点。

    • 4

相关问题