主页 / dba / 问题 / 290143
Accepted
Mike S
Asked: 2021-04-21 10:42:13 +0800 CST

如何使用 SQL Server 中 XML 数据中的序号位置（不是名称）解析列名？

  • 772

我在数据库中有数百个具有相同结构的表：

一些Id、Pos、 不同数量的其他字段

因此，例如，一个表可能如下所示：

PersonId, Pos, Hobby, Degree
12345, 1, Basketball, Bachelor of Science
12345, 2, Baseball, Master of Science
12345, 3, Boxing, NULL
12345, 4, Tennis, NULL
22222, 1, Golf, Bachelor of Science
22222, 2, NULL, Master of Science
22222, 3, NULL, Doctorate

我想汇总每列 3-N 的值。所以这会变成：

12345, "Basketball, Baseball, Boxing, Tennis",  "Bachelor of Science, Master of Science"
22222, "Golf", "Bachelor of Science, Master of Science, Doctorate"

另一个表可能如下所示：

ClientId, Pos, Location, Language, CommunicationType
33333, 1, North Carolina, English, Phone
33333, 2, New York, Spanish, Email
33333, 3, NULL, Portuguese, NULL
44444, 1, California, English, Phone
44444, 2, NULL, NULL, Email

变成这样：

33333, "North Carolina, New York", "English, Spanish, Portugeue", "Phone, Email"
44444, "California", "English", "Phone, Email"

我想做的是创建一个 TVF，我可以在其中指定表名并让函数返回其字段。理想情况下，就像我刚刚在上面演示的那样卷起来。

Solomon Rutzky 提供了一个解决方案（SQL Server：将表名作为参数传递给表值函数），他展示了如何使用 XML 和 CASE 语句在 TVF 中接受表名。

这是一个改编：

DECLARE @TableName sysname = 'objects'
/*
DECLARE @TableName sysname = 'columns'
DECLARE @TableName sysname = 'indexes'
*/
       
SELECT tab.BaseData.value(N'/row[1]/@name', N'VARCHAR(128)') AS [name],
       tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [object_id],
       *
FROM (
    SELECT CASE @TableName
             WHEN N'objects' THEN (SELECT * FROM master.sys.tables FOR XML RAW, TYPE)
             WHEN N'indexes' THEN (SELECT * FROM master.sys.indexes FOR XML RAW, TYPE)
             WHEN N'columns' THEN (SELECT * FROM master.sys.columns FOR XML RAW, TYPE)
           END AS [BaseData]
     ) tab;

如果我要创建一个怪物 CASE 语句并考虑所有可能的传入表名，有没有办法按序号位置（而不是像我上面那样的名称）引用列？更好的是，也可以汇总并划定它们的值（这是我的最终目标）？

先感谢您！

此外，这里是创建我的两个示例表的 DDL：

CREATE TABLE [dbo].[Person](
[PersonId] [int] NULL,
[Pos] [int] NULL,
[Hobby] [varchar](100) NULL,
[Degree] [varchar](50) NULL
)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 1, N'Basketball', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 2, N'Baseball', N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 3, N'Boxing', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 4, N'Tennis', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 1, N'Golf', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 2, NULL, N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 3, NULL, N'Doctorate')
GO

CREATE TABLE [dbo].[Client](
[ClientId] [int] NULL,
[Pos] [int] NULL,
[Location] [varchar](100) NULL,
[Language] [varchar](50) NULL,
[CommunicationType] [varchar](50) NULL
)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 1, N'North Carolina', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 2, N'New York', N'Spanish', N'Email')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 3, NULL, N'Portuguese', NULL)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 1, N'California', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 2, NULL, NULL, N'Email')
GO

SELECT * FROM Person;
SELECT * FROM Client;
sql-server t-sql

3 个回答

  1. Best Answer

    有没有办法按序号位置引用列

    是的，但我不确定这将如何帮助你做你想做的事。您将序数位置放在谓词中，就像您已经为row[1].

    改为'/row[1]/@name'获取第三列将如下所示'/row[1]/@*[3]'。您应该知道空值不会创建任何属性，因此第三个属性中的数据并不总是来自第三列。

    要解决此问题，您可以为列值生成元素而不是属性，并用于XSINIL获取列中空值的空元素，例如：SELECT * FROM master.sys.indexes FOR XML RAW, ELEMENTS XSINIL, TYPE. 然后您需要从 XML 中选择第三个元素而不是第三个属性'/row[1]/*[3]'。

    您已经在“创建一个怪物 CASE 语句并考虑所有可能的传入表名”的路径上，所以为什么不创建一个怪物查询来代替您想要的东西，而不需要 XML 的东西。

    select T.PersonId as Id,
       '"' + string_agg(T.Hobby, ',') within group (order by T.Pos) + '", ' +
       '"' + string_agg(T.Degree, ',') within group (order by T.Pos) + '"' as Value
from dbo.Person as T
where @TableName = N'Person'
group by T.PersonId
union all
select T.ClientId,
       '"' + string_agg(T.Location, ',') within group (order by T.Pos) + '", ' +
       '"' + string_agg(T.Language, ',') within group (order by T.Pos) + '", ' +
       '"' + string_agg(T.CommunicationType, ',') within group (order by T.Pos) + '"'
from dbo.Client as T
where @TableName = N'Client'
group by T.ClientId;

    如果您需要经常甚至自动更新函数，则可以对元表使用动态 SQL 来生成上述查询。

    由于您使用的是 SQL Server 2016，因此string_agg()您不需要使用它for xml path来进行连接。查询变大了，但原理相同，仍然可以使用动态 SQL 创建。

    select T.PersonId as Id,
       '"' + stuff((
                    select ', '+T2.Hobby 
                    from dbo.Person as T2 
                    where T.PersonId = T2.PersonId 
                    order by T2.Pos 
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
       '"' + stuff((
                    select ', '+T2.Degree 
                    from dbo.Person as T2
                    where T.PersonId = T2.PersonId 
                    order by T2.Pos 
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"' as Value
from dbo.Person as T
where @TableName = N'Person'
group by T.PersonId
union all
select T.ClientId,
       '"' + stuff((
                    select ', '+T2.Location 
                    from dbo.Client as T2 
                    where T.ClientId = T2.ClientId 
                    order by T2.Pos 
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
       '"' + stuff((
                    select ', '+T2.Language
                    from dbo.Client as T2 
                    where T.ClientId = T2.ClientId
                    order by T2.Pos 
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
       '"' + stuff((
                    select ', '+T2.CommunicationType
                    from dbo.Client as T2 
                    where T.ClientId = T2.ClientId
                    order by T2.Pos 
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"'
from dbo.Client as T
where @TableName = N'Client'
group by T.ClientId;
    • 2

  2. 您不能在此处使用动态 SQL，因为这在 TVF 中不起作用。不过，您可以使用动态生成下面的实际代码。

    鉴于您在 SQL Server 2016 上，您没有STRING_AGG可用的，因此您将不得不使用FOR XML/STUFF方法，这对于多列非常复杂。

    不必为每一列再次查询数据，也不必执行，您可以使用APPLY和的组合.value

    CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
    SELECT PersonId AS Id,
        '"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
    FROM (SELECT DISTINCT PersonId FROM Person) t1
    CROSS APPLY (VALUES((
            SELECT col1, col2, col3
            FROM Person t2
            WHERE t2.PersonId = t1.PersonId
            ORDER BY t2.Pos
            FOR XML PATH (''), TYPE
        ))) v(XmlValues)
    WHERE @Tablename = 'Person'
   UNION ALL
    SELECT ClientId,
        '"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
        '"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
    FROM (SELECT DISTINCT ClientId FROM Client) t1
    CROSS APPLY (VALUES((
            SELECT col1, col2, col3
            FROM Client t2
            WHERE t2.ClientId = t1.ClientId
            ORDER BY t2.Pos
            FOR XML PATH (''), TYPE
        ))) v(XmlValues)
    WHERE @Tablename = 'Client'
   UNION ALL
......
);

GO

    重申一下，您需要替换col1,col2为实际的列名，与表名相同。您不能在函数中使用动态 SQL 来执行此操作。

    为了完整起见，我将展示STRING_AGG更简单的方法：

    CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
    SELECT PersonId AS Id,
        '"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
    FROM Person
    WHERE @Tablename = 'Person'
    GROUP BY PersonId
   UNION ALL
    SELECT ClientId,
        '"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
        '"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
    FROM Client
    WHERE @Tablename = 'Client'
    GROUP BY ClientId
   UNION ALL
......
);

GO
    • 1

  3. 我不相信使用函数可以完全按照您的意愿行事，因为它必须具有固定的输出形状（数字、类型和列名）。

    一种可能的近似方法是返回固定数量的列（具有通用名称），每个列都包含字符串的聚合，对于不适用于列数少于最大值的源表的额外列，返回 null。

    如其他答案中所述，STRING_AGG这是理想的选择，但在 SQL Server 2016 中不可用。如链接的问答中所述，SQL CLR 流式表值函数可以提供有效的替代品。现在，我知道你了'会说无论出于何种原因你都不能使用 SQL CLR，但是为了将来有类似要求的读者的利益，这里是一个示例实现。

    出于技术原因，代码使用环回连接，因此前几个参数指定服务器/实例名称和数据库名称。第三个参数是表格，预计包含一个整数id列，第二个位置是一个整数排序列。其余列均假定为字符串。

    此示例实现仅限于五个这样的列。它对表进行一次有序传递，并通过一次仅将当前组保留在内存中来最小化内存使用量。它应该比解决方案快得多XML PATH。

    示例调用和结果

    SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Person'
) AS GTD;
    ID col1 col2 col3 col4 col5
    12345 篮球、棒球、拳击、网球 理学学士、理学硕士 无效的 无效的 无效的
    22222 高尔夫球 理学学士、理学硕士、博士 无效的 无效的 无效的 
    SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Client'
) AS GTD;
    ID col1 col2 col3 col4 col5
    33333 北卡罗来纳，纽约 英语、西班牙语、葡萄牙语 电话、电子邮件 无效的 无效的
    44444 加利福尼亚 英语 电话、电子邮件 无效的 无效的

    T-SQL

    CREATE ASSEMBLY [DBA] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E8B986600000000000000000E00022200B013000001C00000006000000000000123A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000C03900004F00000000400000A802000000000000000000000000000000000000006000000C000000883800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000181A000000200000001C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000F439000000000000480000000200050018260000701200000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000761FFE730400000625027D0500000425037D0700000425047D090000042A000013300300710000000100001102A50300001B0A03067B1000000A810D00000104067B1100000A16A30E000001810E00000105067B1100000A17A30E000001810E0000010E04067B1100000A18A30E000001810E0000010E05067B1100000A19A30E000001810E0000010E06067B1100000A1AA30E000001810E0000012A1E02281200000A2A6602281200000A02037D0100000402281300000A7D030000042A001B3002003400000002000011027B010000040A061FFC5917360606175917351F00061FFC2E08061759173602DE1100DE0E022808000006DC022807000006DC2A011C000002002300022500070000000002001500172C0007000000001B300500BD03000003000011027B010000040B074503000000070000008B02000083030000160ADD9B03000002157D01000004731400000A25027B040000046F1500000A25027B060000046F1600000A25176F1700000A251B6F1800000A25166F1900000A0C02086F1A00000A731B00000A7D0A000004021FFD7D01000004027B0A0000046F1C00000ADE0F130572010000701105731D00000A7A027239000070027B08000004725F000070281E00000A027B0A000004731F00000A7D0B000004021FFC7D01000004027B0B0000046F2000000A0D7E2100000A096F2200000A2C3B1B8D1F00000125167271000070A22517027B08000004A225187281000070A22519027B06000004A2251A72B3000070A2282300000A732400000A7A027B0B00000472B7000070027B0800000472D5000070281E00000A6F2500000A02027B0B0000046F2600000A7D0C00000402027B0C0000046F2700000A18597D0D000004027B0D0000041B312C72F50000707209010070027B08000004027B0D0000048C240000011B8C24000001282800000A732900000A7A02027B0D0000048D1A0000017D0E000004021B8D0E0000017D0F0000041613062B35027B0F00000411067E2A00000AA40E0000011106027B0D0000042F13027B0E00000411062000010000732B00000AA211061758130611061B32C67E2C00000A1304384201000002027B0C000004166F2D00000A7D10000004027C10000004282E00000A3A14010000027C1000000411048C0D000001FE160D0000016F2200000A3A8D0000001204282E00000A2D721613072B35027B0F0000041107027B0E00000411079A6F2F00000A733000000AA40E000001027B0E00000411079A6F3100000A261107175813071107027B0D00000432C1021104027B0F000004733200000A8C0300001B7D0200000402177D01000004170ADD17010000021FFC7D010000041204027C10000004283300000A283400000A1613082B5B027B0C000004110818586F3500000A13091209283600000A2D3B027B0E00000411089A6F3700000A173114027B0E00000411089A727D0100706F3800000A26027B0E00000411089A1209283900000A6F3800000A261108175813081108027B0D000004329B027C10000004FE150D000001027B0C0000046F3A00000A3AAEFEFFFF16130A2B26027B0F000004110A027B0E000004110A9A6F2F00000A733000000AA40E000001110A1758130A110A027B0D00000432D0021104027B0F000004733200000A8C0300001B7D0200000402187D01000004170ADE1F021FFC7D01000004160A022808000006022807000006DE07022805000006DC062A0000004134000000000000730000000D000000800000000F000000140000010400000000000000B4030000B403000007000000000000006E02157D01000004027B0A0000042C0B027B0A0000046F0800000A2A72021FFD7D01000004027B0B0000042C0B027B0B0000046F0800000A2A1E027B020000042A1A733B00000A7A133002004F00000006000011027B010000041FFE3318027B03000004281300000A330B02167D01000004020A2B071673040000060A06027B050000047D0400000406027B070000047D0600000406027B090000047D08000004062A1E02280C0000062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000044050000237E0000B00500003007000023537472696E677300000000E00C00008401000023555300640E0000100000002347554944000000740E0000FC03000023426C6F6200000000000000020000015717A20B0902000000FA013300160000010000002600000003000000100000000D0000000B000000050000003B000000100000000600000001000000020000000200000007000000030000000100000002000000010000000000420301000000000006009F0203050600D102030506004002F0040F00230500000600840562030600FA0162030600540203050A008A0252040A008F0152040A00890152040600C1014E050A00BF0252040A004C0032050A00FC0232050600640062030600250203050600CD01620306006D044E050A003704B0050600C3036203060022001A01060014001A010A007B03B0050A007701B0050A000104B00506004404EF0606007202F0040600C60562030A001D04CD030A006E03CD0306001D0362030A006D01CD030600C00662030600BB0362030A00F403CD0306004F00620306009F03620306008903620300000000EF0000000000010001000100100061050000150001000100030110000100000015000100040001001A02E7000100A406980001005001E7000100A301E40006009E01E4000100ED01E4000600E801E4000100DE01E4000600D901E40001005500F80001008A00FC0001009D0000010100AF00E7000100C20004010100CB0009010100E600C100502000000000960004010E01010070200000000096000B0716010400ED20000000008618EA0406000B00F520000000008618EA0401000B00102100000000E101FF0106000C006C2100000000E101E60616000C006C250000000081003B0006000C0088250000000081007C0006000C00A52500000000E109370625000C00AD2500000000E1018B0506000C00A52500000000E109790625000C00B42500000000E10179042D010C000F2600000000E101BD0438000C0000000100A30100000200ED0100000300DE01000001001307020002006A0102000300300002000400710002000500980002000600AA0002000700BD00000001001A0203000A0003002D000300060003004500030049000900EA0401001100EA0406001900EA040A003900EA0410004100EA0406006100EA0406008100EA0406008900120206009100E60616000C00980620009100AA0506009100980625001400DC042F005900DC043800D900EA0406001C00350053001C00760057002900EA040600E10035015B009900EA0406009900B2017500990024037500990017077A009900D30601009900C8067A00E90006037F00B900EA047500F10069030600A100EA048300F90076058A00C100EA0491000101E6032500090150039800290047059B00F9007605A0001101EA0475000101FB067500C1000F04A6001901B106AB00F9007D05AF002901EA04B70071005D03BD00D100EA04010069005D03C100C9004900C50069005703160029001B037F007100EA047500D100E003CB001C00EA04D0006900EF02AB006900EA040100C900F902D800710057031600D1003703AB00D1008201DE007100EF027F001901650116003101EA040600200023006A0120002B00970124003300D5032E000B0039012E00130042012E001B00610144003300D50363003B00D00364003300E80380007B00D003A0007B00D00320017B00D00340017B00D00360017B00D00380017B00D003A0017B00D0033D005F006300E400E700EA00030001000000D205350100001006350102000900030002000B00050003000A00110003000C001300030012001500030014001700030016001900030018001B0003001A001D001A0029004900048000000000000000000000000000000000DC000000040000000000000000000000EF00110100000000040000000000000000000000EF00F800000000000300020000000000003C4765745461626C65446174613E645F5F300049456E756D657261626C6560310049456E756D657261746F72603100636F6C31004974656D31003C3E6D5F5F46696E616C6C79310047657453716C496E743332003C6C6F6F706261636B3E355F5F320056616C75655475706C65603200636F6C32004974656D32003C3E6D5F5F46696E616C6C7932003C636F6D6D616E643E355F5F3300636F6C33003C7265616465723E355F5F3400636F6C34003C636F6C756D6E733E355F5F3500636F6C35003C73623E355F5F36003C53716C537472696E67733E355F5F3700446174616261736537003C69643E355F5F38003C4D6F64756C653E0053797374656D2E44617461004765745461626C6544617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E47656E65726963006765745F43757272656E744D616E616765645468726561644964003C3E6C5F5F696E697469616C54687265616449640052656164006964004462436F6D6D616E640053716C436F6D6D616E6400417070656E640053797374656D446174614163636573734B696E64003C3E335F5F536572766572496E7374616E6365007365745F44617461536F757263650049456E756D657261626C650049446973706F7361626C65003C3E335F5F5461626C654E616D65003C3E335F5F44617461626173654E616D6500547970650053797374656D2E49446973706F7361626C652E446973706F7365003C3E315F5F737461746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C65417474726962757465004974657261746F7253746174654D616368696E6541747472696275746500446562756767657248696464656E4174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650053716C46616365744174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650047657453716C537472696E67006765745F436F6E6E656374696F6E537472696E6700546F537472696E67007365745F496E697469616C436174616C6F67006765745F4C656E677468004461746162617365372E646C6C0044424E756C6C006765745F49734E756C6C0053797374656D004F70656E004462436F6E6E656374696F6E0053716C436F6E6E656374696F6E004E6F74537570706F72746564457863657074696F6E00417267756D656E744F75744F6652616E6765457863657074696F6E00417267756D656E74457863657074696F6E0053797374656D2E446174612E436F6D6D6F6E00436C65617200457865637574655363616C6172004462446174615265616465720053716C446174615265616465720045786563757465526561646572004462436F6E6E656374696F6E537472696E674275696C6465720053716C436F6E6E656374696F6E537472696E674275696C646572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C53797374656D2E4F626A6563743E2E476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300457175616C730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E63617400466F726D6174004F626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740053797374656D2E446174612E53716C436C69656E7400456E7669726F6E6D656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E6765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C3E325F5F63757272656E74006765745F4669656C64436F756E7400436F6E76657274007365745F456E6C697374007365745F436F6E6E65637454696D656F7574004D6F76654E6578740053797374656D2E54657874007365745F436F6D6D616E64546578740046696C6C526F7700726F77007365745F496E7465677261746564536563757269747900000000374C006F006F0070006200610063006B00200063006F006E006E0065006300740069006F006E0020006600610069006C00650064002E000025530045004C0045004300540020004F0042004A004500430054005F004900440028002700011127002C00200027005500270029003B00010F5400610062006C006500200027000131270020006E006F007400200066006F0075006E006400200069006E00200064006100740061006200610073006500200001032E00001D530045004C0045004300540020002A002000460052004F004D002000001F20004F005200440045005200200042005900200031002C00200032003B0000135400610062006C0065004E0061006D00650000735400610062006C00650020007B0030007D00200068006100730020007B0031007D0020006100640064006900740069006F006E0061006C00200063006F006C0075006D006E0073002E00200054006800650020006D006100780069006D0075006D0020006900730020007B0032007D002E0000052C00200000002361A2CFA2AF2F4BB45B977B78FEFDCA000420010108032000010520010111110520010112190320000205151255011C04200013000320001C05151259011C08200015125501130004200012490B070115113D0211351D11390915113D0211351D11390306130003061301030000080307010811070B0208124D1C11351251080808113908042001010E04200101020320000E062002010E12510600030E0E0E0E062002010E125D02061C042001021C0500010E1D0E0420001265032000080700040E0E1C1C1C052002010E0E03061139030611350520011135080420001269072002011300130105200111390805200112690E02060E020608040701120C08B77A5C561934E0890306125D030612610306126504061D126904061D1139070003122D0E0E0E160007011C101135101139101139101139101139101139072000151255011C0328001C0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000002C01002755736572446566696E656446756E6374696F6E732B3C4765745461626C65446174613E645F5F3000008237010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E80E10D0A202020202020202020202020696420696E7465676572204E554C4C2C0D0A202020202020202020202020636F6C31206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C32206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C33206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C34206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C35206E76617263686172286D617829204E554C4C0D0A20202020040100000012010001005408074D617853697A658000000012010001005408074D617853697A65010100000000000000E8B9866000000000020000001C010000A4380000A41A0000525344533A0A7AF47A862C41B751DA4E601A730601000000433A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C736F757263655C7265706F735C4461746162617365375C4461746162617365375C6F626A5C52656C656173655C4461746162617365372E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E83900000000000000000000023A0000002000000000000000000000000000000000000000000000F4390000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650037002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650037002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000143A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE OR ALTER FUNCTION dbo.GetTableData
(
    @ServerInstance [nvarchar](128), 
    @DatabaseName [nvarchar](128), 
    @TableName [nvarchar](257)
)
RETURNS TABLE 
(
    id integer NULL,
    col1 nvarchar(max) NULL,
    col2 nvarchar(max) NULL,
    col3 nvarchar(max) NULL,
    col4 nvarchar(max) NULL,
    col5 nvarchar(max) NULL
)
ORDER (id)
AS EXTERNAL NAME [DBA].[UserDefinedFunctions].[GetTableData];

    C# 源代码

    using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.None,
        FillRowMethodName = "FillRow",
        TableDefinition = @"
            id integer NULL,
            col1 nvarchar(max) NULL,
            col2 nvarchar(max) NULL,
            col3 nvarchar(max) NULL,
            col4 nvarchar(max) NULL,
            col5 nvarchar(max) NULL
    ")]
    public static IEnumerable GetTableData
    (
        [SqlFacet(MaxSize = 128)] string ServerInstance,
        [SqlFacet(MaxSize = 128)] string DatabaseName,
        [SqlFacet(MaxSize = 257)] string TableName
    )
    {
        const string COMMA_SPACE = ", ";
        const int MAX_OUTPUT_COLS = 5;
        const int FIXED_COLS = 2;

        // Establish loopback connection
        var csb = new SqlConnectionStringBuilder
        {
            DataSource = ServerInstance,
            InitialCatalog = DatabaseName,
            IntegratedSecurity = true,
            ConnectTimeout = 5,
            Enlist = false
        };

        using var loopback = new SqlConnection(csb.ConnectionString);
        try
        {
            loopback.Open();
        }
        catch (Exception e)
        {
            throw new Exception("Loopback connection failed.", e);
        }

        // Check supplied table name exists and is accessible
        using var command = new SqlCommand($@"SELECT OBJECT_ID('{TableName}', 'U');", loopback);

        object object_id = command.ExecuteScalar();

        if (Convert.DBNull.Equals(object_id))
        {
            throw new ArgumentException($@"Table '{TableName}' not found in database {DatabaseName}.");
        }

        // Read table in the required order
        command.CommandText = $@"SELECT * FROM {TableName} ORDER BY 1, 2;";
        SqlDataReader reader = command.ExecuteReader();

        // Number of columns to process (skipping id and position)
        int columns = reader.FieldCount - FIXED_COLS;

        if (columns > MAX_OUTPUT_COLS)
        {
            throw new ArgumentOutOfRangeException(
                nameof(TableName),
                $@"Table {TableName} has {columns} additional columns. The maximum is {MAX_OUTPUT_COLS}.");
        }

        // Create a string builder and SqlString array element for each output column
        StringBuilder[] sb = new StringBuilder[columns];
        SqlString[] SqlStrings = new SqlString[MAX_OUTPUT_COLS];

        for (int i = 0; i < MAX_OUTPUT_COLS; i++)
        {
            SqlStrings[i] = SqlString.Null;
            if (i < columns)
            {
                sb[i] = new StringBuilder(256);
            }
        }

        // Remember the current id value
        SqlInt32 previous_id = SqlInt32.Null;

        // Process each row
        while (reader.Read())
        {
            SqlInt32 id = reader.GetSqlInt32(0);

            if (!id.IsNull)
            {
                if (!id.Equals(previous_id))
                {
                    if (!previous_id.IsNull)
                    {
                        // Completed a group
                        for (int i = 0; i < columns; i++)
                        {
                            SqlStrings[i] = new SqlString(sb[i].ToString());
                            sb[i].Clear();
                        }

                        // Output the completed group
                        yield return (previous_id, SqlStrings);
                    }
                    previous_id = new SqlInt32(id.Value);
                }

                // Add the current row to the string builders
                for (int i = 0; i < columns; i++)
                {
                    var s = reader.GetSqlString(i + FIXED_COLS);
                    if (!s.IsNull)
                    {
                        if (sb[i].Length > 1)
                        {
                            sb[i].Append(COMMA_SPACE);
                        }
                        sb[i].Append(s.Value);
                    }
                }
            }
        }

        // Final group
        for (int i = 0; i < columns; i++)
        {
            SqlStrings[i] = new SqlString(sb[i].ToString());
        }

        // Output the completed group
        yield return (previous_id, SqlStrings);
    }

    // Called by SQL Server to populate each row returned from the TVF
    public static void FillRow
        (
        object row,
        out SqlInt32 id,
        out SqlString col1,
        out SqlString col2,
        out SqlString col3,
        out SqlString col4,
        out SqlString col5
        )
    {
        (SqlInt32 id, SqlString[] cols) v = ((SqlInt32, SqlString[]))row;
        id = v.id;
        col1 = v.cols[0];
        col2 = v.cols[1];
        col3 = v.cols[2];
        col4 = v.cols[3];
        col5 = v.cols[4];
    }
}
    • 1

相关问题