将 SQL(SELECT 查询)转换为 XML 表单的 API 或工具 [英] API or tools to convert SQL (SELECT queries) into XML FORM

问题描述

需要帮助将 SQL 查询转换为某种标准 XML 格式.

Need help to convert SQL queries to some sort of standard XML format.

例如，假设我有一个查询SELECT A, B, C FROM XYZ WHERE B = C AND A > 2"

For example, lets say I have a query "SELECT A, B, C FROM XYZ WHERE B = C AND A > 2"

我应该得到的结果

<QUERY>
<SELECT>
    <COLUMNS>
        <COLUMN>A</COLUMN>
        <COLUMN>B</COLUMN>
        <COLUMN>B</COLUMN>
    </COLUMNS>
</SELECT>
<FROM>
    <TABLES>
    </TABLES>
</FROM>
<WHERE>
    <CONDITIONS>
        <AND>
            <CONDITION>
                <EQUALS>
                <VARIABLE>B</VARIABLE>
                <VARIABLE>C</VARIABLE>
                </EQUALS>
            </CONDITION>
            <CONDITION>
                <GREATER>
                <VARIABLE>A</VARIABLE>
                <VARIABLE>2</VARIABLE>
                </GREATER>
            </CONDITION>
        </AND>
    </CONDITIONS>
</WHERE>
</QUERY>

请给我推荐一些可以为我提供此 XML 结果的 API 或工具.

Please suggest me some API or tools which can provide me this XML result.

推荐答案

这看起来很有趣.虽然我的回复晚了，但我尝试了以下方法，看看我是否可以接近想出一种方法来做到这一点.它需要更多的工作，但我相信通过使用这种格式，您可以提出一个可行的解决方案.

This looks interesting. Although late in my response, I tried the following to see if I could come close to coming up with a way to do this. It needs more work, but I believe that by using this format you can come up with a workable solution.

    SET NOCOUNT ON

    ------------------------------------------------------------------------------
    ----------- DECLARE VARIABLES AND TABLES -------------------------------------
    ------------------------------------------------------------------------------
    DECLARE @QueryID                  INT
    DECLARE @SelectID                 INT
    DECLARE @ColumnsBeginID           INT
    DECLARE @ColumnsEndID             INT
    DECLARE @FromID                   INT
    DECLARE @TablesBeginID            INT
    DECLARE @TablesEndID              INT
    DECLARE @WhereID                  INT
    DECLARE @ConditionsBeginID        INT
    DECLARE @ConditionsEndID          INT

    DECLARE @XMLQuery                 XML = '<QUERY></QUERY>'


    DECLARE @Query TABLE
    (
          QueryID                     INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [SELECT]                    INT             NOT NULL    
        , [FROM]                      INT             NOT NULL    
        , [WHERE]                     INT                 NULL      
    )

    DECLARE @Select TABLE
    (
          SelectID                    INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [COLUMNS_BEGIN]             INT             NOT NULL    
        , [COLUMNS_END]               INT             NOT NULL    
    )

    DECLARE @Column TABLE
    (
          ColumnID                    INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [Column]                    SYSNAME         NOT NULL
        , [TableAlias]                SYSNAME         NOT NULL
        , [ColumnAlias]               SYSNAME         NOT NULL
    )

    DECLARE @From TABLE
    (
          FromID                      INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [TABLES_BEGIN]              INT             NOT NULL    
        , [TABLES_END]                INT             NOT NULL    
    )

    DECLARE @Table TABLE
    (
          TableID                      INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [Join]                       SYSNAME         NOT NULL    
        , [Tablename]                  SYSNAME         NOT NULL    
        , [TableAlias]                 SYSNAME         NOT NULL    
        , [On]                         SYSNAME         NOT NULL    
    )

    DECLARE @Where TABLE
    (
          WhereID                     INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [CONDITIONS_BEGIN]          INT             NOT NULL    
        , [CONDITIONS_END]            INT             NOT NULL    
    )

    DECLARE @Condition TABLE
    (
          ConditionID                 INT             NOT NULL    IDENTITY (1,1)   PRIMARY KEY
        , [AndOr]                     SYSNAME         NOT NULL    
        , [Equality]                  SYSNAME         NOT NULL
        , [Variable1]                 SYSNAME         NOT NULL    
        , [Variable2]                 SYSNAME         NOT NULL    
    )

    ------------------------------------------------------------------------------
    ----------- USERS QUERY HERE -------------------------------------------------
    ------------------------------------------------------------------------------

    --SELECT A, B, C FROM XYZ WHERE B = C AND A > 2

    INSERT INTO @Column ([Column], [TableAlias], [ColumnAlias]) VALUES ('A', 'x', 'a')
    SET @ColumnsBeginID = @@IDENTITY 
    INSERT INTO @Column ([Column], [TableAlias], [ColumnAlias]) VALUES ('B', 'x', 'b')
    INSERT INTO @Column ([Column], [TableAlias], [ColumnAlias]) VALUES ('C', 'x', 'c')
    SET @ColumnsEndID = @@IDENTITY 

    INSERT INTO @Select ([COLUMNS_BEGIN], [COLUMNS_END]) VALUES (@ColumnsBeginID, @ColumnsEndID)
    SET @SelectID = @@IDENTITY


    INSERT INTO @Table ([Join], [Tablename], [TableAlias], [On]) VALUES ('FROM', 'XYZ', 'x', '')
    SET @TablesBeginID = @@IDENTITY
    SET @TablesEndID = @TablesBeginID

    INSERT INTO @From ([TABLES_BEGIN], [TABLES_END]) VALUES (@TablesBeginID, @TablesEndID)
    SET @FromID = @@IDENTITY


    INSERT INTO @Condition ([AndOr], [Equality], [Variable1], [Variable2]) VALUES ('AND','EQUALS','B','C')
    SET @ConditionsBeginID = @@IDENTITY
    INSERT INTO @Condition ([AndOr], [Equality], [Variable1], [Variable2]) VALUES ('AND','GREATER','A','2')
    SET @ConditionsEndID = @@IDENTITY

    INSERT INTO @Where ([CONDITIONS_BEGIN], [CONDITIONS_END]) VALUES (@ConditionsBeginID, @ConditionsEndID)
    SET @WhereID = @@IDENTITY


    INSERT INTO @Query ([SELECT], [FROM], [WHERE]) VALUES (@SelectID, @FromID, @WhereID)


    ------------------------------------------------------------------------------ 
    ----------- BUILD THE @XMLQuery VARIABLE ------------------------------------- 
    ------------------------------------------------------------------------------ 

    SET @XMLQuery = (SELECT SelectColumn            = c.[Column]
                          , SelectColumnTableAlias  = c.[TableAlias]
                          , SelectColumnColumnAlias = c.[ColumnAlias]

                          , FromTableJoin           = t.[Join]
                          , FromTable               = t.[Tablename]
                          , FromTableAlias          = t.[TableAlias]
                          , FromOn                  = t.[On]

                          , WhereConditionAndOr     = n.[AndOr]     
                          , WhereConditionEquaility = n.[Equality]  
                          , WhereConditionVariable1 = n.[Variable1] 
                          , WhereConditionVariable2 = n.[Variable2] 

                       FROM  @Query     q
                       JOIN  @Select    s  ON s.SelectID    = q.[SELECT]
                       JOIN  @From      f  ON f.FromID      = q.[FROM]
                       JOIN  @Where     w  ON w.WhereID     = q.[WHERE]
                       JOIN  @Column    c  ON c.ColumnID    BETWEEN s.[COLUMNS_BEGIN]    AND s.[COLUMNS_END]
                       JOIN  @Table     t  ON t.TableID     BETWEEN f.[TABLES_BEGIN]     AND f.[TABLES_END]
                       JOIN  @Condition n  ON n.ConditionID BETWEEN w.[CONDITIONS_BEGIN] AND w.[CONDITIONS_END]

                   ORDER BY c.ColumnID
                          , t.TableID
                          , n.ConditionID
                        FOR XML AUTO, ROOT ('QUERY') 
                        --FOR XML RAW, ROOT ('QUERY') 
                        --FOR XML PATH, ROOT ('QUERY') 
                      )

    SELECT @XMLQuery

这篇关于将 SQL(SELECT 查询)转换为 XML 表单的 API 或工具的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！