Como analisar nomes de colunas usando posição ordinal (não nome) de dados XML no SQL Server?

existe uma maneira de referenciar as colunas por posição ordinal

Sim, existe, mas não tenho certeza de como isso ajudaria você a fazer o que deseja. Você coloca a posição ordinal no predicado, como já faz para row[1].

Alterar '/row[1]/@name'para obter a terceira coluna seria semelhante a '/row[1]/@*[3]'. Você deve estar ciente de que valores nulos não criam nenhum atributo, portanto, seus dados no terceiro atributo nem sempre virão da terceira coluna.

Para corrigir isso, você poderia gerar elementos em vez de atributos para valores de coluna e usar XSINILpara obter elementos vazios para valores nulos em colunas, ex: SELECT * FROM master.sys.indexes FOR XML RAW, ELEMENTS XSINIL, TYPE. Então você precisa selecionar o terceiro elemento do XML em vez do terceiro atributo '/row[1]/*[3]'.

Você já está no caminho para "criar uma instrução CASE monstruosa e contabilizar todos os nomes de tabelas de entrada possíveis" , então por que não criar uma consulta monstruosa que faça o que você deseja, sem o material 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;

Você pode usar SQL dinâmico em meta tabelas para gerar a consulta acima se estiver em uma situação em que precise atualizar a função com frequência ou até mesmo automaticamente.

Como você está no SQL Server 2016, não string_agg()precisa usar for xml pathpara fazer a concatenação. A consulta ficou maior, mas é o mesmo princípio e ainda pode ser criada usando SQL dinâmico.

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;

Você não pode usar SQL dinâmico aqui, pois isso não funcionará dentro de um TVF. Você pode usar dynamic para gerar o código real abaixo.

Dado que você está no SQL Server 2016, você não tem STRING_AGGdisponível, então você terá que usar o FOR XML/STUFFmétodo, que é bastante complexo com várias colunas.

Não é necessário ou eficiente continuar consultando os dados novamente para cada coluna, você pode usar uma combinação de APPLYe.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

Para reiterar, você precisa substituir col1,col2pelos nomes reais das colunas, o mesmo que os nomes das tabelas. Você não pode fazer isso com SQL dinâmico em uma função.

Para completar, vou mostrar o STRING_AGGmétodo que é muito mais simples:

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

Eu não acredito que seja possível fazer exatamente o que você quer com uma função, porque ela deve ter uma forma de saída fixa (número, tipos e nomes de colunas).

Uma aproximação possível é retornar um número fixo de colunas (com nomes genéricos), cada uma contendo uma agregação de strings, com null retornado para as colunas extras não aplicáveis ​​a uma tabela de origem com menos colunas que o máximo.

Conforme observado em outras respostas, STRING_AGGé ideal para isso, mas não está disponível no SQL Server 2016. Um substituto eficiente para isso pode ser fornecido por uma função com valor de tabela de streaming SQL CLR, conforme observado nas perguntas e respostas vinculadas. Agora, eu conheço você 'll dizer que você não pode usar SQL CLR por qualquer motivo, mas para o benefício de futuros leitores com um requisito semelhante, aqui está um exemplo de implementação.

O código usa uma conexão de loopback por motivos técnicos, portanto, os primeiros parâmetros especificam o nome do servidor/instância e o nome do banco de dados. O terceiro parâmetro é a tabela, que deve conter uma coluna de id de inteiros e uma coluna de ordenação de inteiros na segunda posição. As colunas restantes são consideradas strings.

Esta implementação de amostra é limitada a cinco dessas colunas. Ele faz uma única passagem ordenada pela tabela e minimiza o uso de memória mantendo apenas o grupo atual na memória de uma só vez. Deve ser muito mais rápido do que as XML PATHsoluções.

Exemplo de chamadas e resultados

SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Person'
) AS GTD;

SELECT 
    GTD.id,
    GTD.col1, 
    GTD.col2, 
    GTD.col3, 
    GTD.col4, 
    GTD.col5
FROM dbo.GetTableData
(
    @@SERVERNAME,
    DB_NAME(),
    N'dbo.Client'
) AS GTD;

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];

Fonte 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];
    }
}