根据条件消除XML中的行 [英] Eliminate row in XML based on criteria

问题描述

以下是用于创建所需数据的脚本:

here's the script to create the needed data:

CREATE TABLE [dbo].[TestXML](
[ID] [int] NOT NULL,
[PID] [int] NULL,
[Code] [int] NULL,
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [decimal](6, 2) NULL,
[Col4] [decimal](6, 2) NULL,
[Col5] [int] NULL,
CONSTRAINT [PK_TestXML] PRIMARY KEY CLUSTERED ([ID] ASC)
)

GO

CREATE TABLE [dbo].[XML_Columns_Reference](
[TableName] nvarchar(10) NULL,
[TableNameXML] nvarchar(10) NULL,
[ColumnName] nvarchar(10) NULL,
[ColumnNameXML] nvarchar(10) NULL,
[ColumnOrder] [int] NULL,
[GroupName] nvarchar(10) NULL,
[GroupOrder] [int] NULL,
[Category] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[XML_NoUseCols_Ref](
[TableNameXML] nvarchar(10) NULL,
[Code] [int] NULL,
[ColumnName] nvarchar(10) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[TestXML] ([ID], [PID], [Code], [Col1], [Col2], [Col3], [Col4], [Col5]) VALUES (1, 1000, 10, 1, 2, CAST(0.20 AS Decimal(6, 2)), CAST(0.10 AS Decimal(6, 2)), 1)
INSERT [dbo].[TestXML] ([ID], [PID], [Code], [Col1], [Col2], [Col3], [Col4], [Col5]) VALUES (2, 1000, 20, NULL, 1, CAST(1.00 AS Decimal(6, 2)), CAST(1.00 AS Decimal(6, 2)), 1)
INSERT [dbo].[TestXML] ([ID], [PID], [Code], [Col1], [Col2], [Col3], [Col4], [Col5]) VALUES (3, 1000, 30, NULL, NULL, CAST(2.00 AS Decimal(6, 2)), CAST(2.00 AS Decimal(6, 2)), 5)

INSERT [dbo].[XML_Columns_Reference] ([TableName], [TableNameXML], [ColumnName], [ColumnNameXML], [ColumnOrder], [GroupName], [GroupOrder], [Category]) VALUES (N'TestXML', N'T', N'Col1', N'N', 1, N'A', 2, NULL)
INSERT [dbo].[XML_Columns_Reference] ([TableName], [TableNameXML], [ColumnName], [ColumnNameXML], [ColumnOrder], [GroupName], [GroupOrder], [Category]) VALUES (N'TestXML', N'T', N'Col2', N'V', 2, N'A', 2, NULL)
INSERT [dbo].[XML_Columns_Reference] ([TableName], [TableNameXML], [ColumnName], [ColumnNameXML], [ColumnOrder], [GroupName], [GroupOrder], [Category]) VALUES (N'TestXML', N'T', N'Col3', N'N', 1, N'B', 3, NULL)
INSERT [dbo].[XML_Columns_Reference] ([TableName], [TableNameXML], [ColumnName], [ColumnNameXML], [ColumnOrder], [GroupName], [GroupOrder], [Category]) VALUES (N'TestXML', N'T', N'Col4', N'V', 2, N'B', 3, NULL)
INSERT [dbo].[XML_Columns_Reference] ([TableName], [TableNameXML], [ColumnName], [ColumnNameXML], [ColumnOrder], [GroupName], [GroupOrder], [Category]) VALUES (N'TestXML', N'T', N'Col5', N'A', 1, N'C', 1, NULL)

INSERT [dbo].[XML_NoUseCols_Ref] ([TableNameXML], [Code], [ColumnName]) VALUES (N'T', 20, N'Col1')
INSERT [dbo].[XML_NoUseCols_Ref] ([TableNameXML], [Code], [ColumnName]) VALUES (N'T', 30, N'Col1')
INSERT [dbo].[XML_NoUseCols_Ref] ([TableNameXML], [Code], [ColumnName]) VALUES (N'T', 30, N'Col2')

这是我运行的查询:

DECLARE @PID int
SET @PID=1000
SELECT
GroupName as [@n],
(SELECT
Code [@n],
(SELECT
ColumnNameXML as [col/@n],
CASE name
WHEN 'Col1' THEN ISNULL(Col1,0)
WHEN 'Col2' THEN ISNULL(Col2,0)
WHEN 'Col3' THEN ISNULL(Col3,0)
WHEN 'Col4' THEN ISNULL(Col4,0)
WHEN 'Col5' THEN ISNULL(Col5,0)
END AS [col/col_value]
FROM sys.columns
CROSS JOIN TestXML ls1
INNER JOIN XML_Columns_Reference r1
ON sys.columns.name=r1.columnname   
WHERE object_id = OBJECT_ID('TestXML') and r1.TableNameXML='T'
and ls1.PID=@PID and ls1.ID=ls2.ID and r1.GroupName=r2.GroupName    
and r1.ColumnName NOT IN (SELECT [ColumnName] 
FROM XML_NoUseCols_Ref  
WHERE TableNameXML='T' and [Code]=ls1.Code
GROUP BY ColumnName)
ORDER BY column_id
FOR XML PATH(''),TYPE
)
FROM TestXML ls2
WHERE ls2.PID=@PID  
FOR XML PATH('cat'),TYPE
)
FROM sys.columns
CROSS JOIN TestXML ls3
INNER JOIN XML_Columns_Reference r2
ON sys.columns.name=r2.columnname
WHERE object_id = OBJECT_ID('TestXML') and ls3.PID=@PID and r2.TableNameXML='T'
GROUP BY GroupName
ORDER BY MIN(groupOrder)
FOR XML PATH('grp'),TYPE

结果:

我需要的是在XML_NoUseCols_Ref中有一个对组中所有行的引用(您可以在XML_Columns_Reference中获得组)以消除XML中的cat行. 在示例中，grp n ="A"

What I need is when there is a reference in XML_NoUseCols_Ref for all the rows of a group (you can get group in XML_Columns_Reference) to eliminated the cat row in the XML. In the example that would be cat n="30" in grp n="A"

任何帮助将不胜感激. 谢谢.

Any kind of help would be appreciated. Thank you.

推荐答案

我建议不要使用CTE(例如临时VIEW)在许多地方重复逻辑来预取数据.其余的是带有相关子查询的简单嵌套，并且更好地阅读:

Instead of repeating the logic in many places I'd suggest to use a CTE (something like an ad-hoc VIEW) to pre-fetch your data. The rest is rather simple nesting with correlated sub-queries and much better to read:

希望我能正确理解您的逻辑...

Hope I got your logic correctly...

WITH cte AS
(       
        SELECT ls1.ID,ls1.PID,ls1.Code
              ,r1.ColumnNameXML,r1.GroupName,r1.GroupOrder
              ,CASE name
                WHEN 'Col1' THEN ISNULL(Col1,0)
                WHEN 'Col2' THEN ISNULL(Col2,0)
                WHEN 'Col3' THEN ISNULL(Col3,0)
                WHEN 'Col4' THEN ISNULL(Col4,0)
                WHEN 'Col5' THEN ISNULL(Col5,0)
                END AS ColumnValue
        FROM sys.columns
        CROSS JOIN TestXML ls1
        INNER JOIN XML_Columns_Reference r1
        ON sys.columns.name=r1.columnname   
        WHERE object_id = OBJECT_ID('TestXML') and r1.TableNameXML='T'
        and ls1.PID=@PID  
        and r1.ColumnName NOT IN (  SELECT [ColumnName] 
                                    FROM XML_NoUseCols_Ref  
                                    WHERE TableNameXML='T' and [Code]=ls1.Code
                                    GROUP BY ColumnName)
) 
SELECT GroupName AS [@n]
      ,(
        SELECT Code AS [@n]
              ,(
                SELECT cte3.ColumnNameXML AS [@n]
                      ,cte3.ColumnValue AS [col_value]
                FROM cte cte3
                WHERE cte3.GroupName=cte1.GroupName AND cte3.Code=cte2.Code
                ORDER BY cte3.ColumnNameXML
                FOR XML PATH('col'),TYPE
               )
        FROM cte cte2
        WHERE cte1.GroupName=cte2.GroupName
        GROUP BY cte2.Code
        ORDER BY cte2.Code
        FOR XML PATH('cat'),TYPE
       )
FROM cte cte1
GROUP BY GroupName,GroupOrder
ORDER BY GroupOrder
FOR XML PATH('grp');

这篇关于根据条件消除XML中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！