Merci de sugestie. Am incercat, insa imi da doua erori. Banuiala mea este ca nu imi accepta parametru de tip table cu OUTPUT. Erorile sunt:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Table'.
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Tabe".
Codul SQL modificat este:
declare @SirSQL as nvarchar(max)
declare
@NOD as nvarchar(100)
DECLARE
@ParmDefinition nvarchar(500)
declare
@Tabel as table(ColoanaXML xml)
set
@NOD='FacturaDetalii'
set
@SirSQL=N'insert into @Tabe
SELECT (SELECT
T.node.query(''.'')
FROM Continut.nodes(''//'
+ @NOD +''') as T(node) for XML auto, root )
FROM [LOG]
WHERE
(SELECT
T.node.query(''.'')
FROM Continut.nodes(''//'
+ @NOD + ''') as T(node) for XML auto, root ) IS NOT NULL'
SET
@ParmDefinition=N'@Tabe as table(Col xml) OUTPUT';
exec
sp_executesql @SirSQL, @ParmDefinition, @Tabe=@Tabel OUTPUT;
SELECT
* FROM @Tabel
Am uitat sa mentionez ca daca folosesc tabel temporar cu ## merge varianta asta cu sql dinamic, numai ca nu ma incalzeste cu nimic, deoarece codul de mai sus vreau sa il pun intr-o functie si de acolo nu pot accesa tabele ##.
Varianta cu tabel temporar (care merge) este cea de mai jos, dar ma lasa rece (si oricum nu imi place prea tare solutia):
declare
@SirSQL as nvarchar(max)
declare
@NOD as nvarchar(100)
DECLARE
@ParmDefinition nvarchar(500)
declare
@Tabel as table(ColoanaXML xml)
set
@NOD='FacturaDetalii'
set
@SirSQL=N'
SELECT (SELECT
T.node.query(''.'')
FROM Continut.nodes(''//'
+ @NOD +''') as T(node) for XML auto, root ) as Rezultat INTO ##Temp
FROM [LOG]
WHERE
(SELECT
T.node.query(''.'')
FROM Continut.nodes(''//'
+ @NOD + ''') as T(node) for XML auto, root ) IS NOT NULL'
exec
sp_executesql @SirSQL
SELECT
* FROM ##Temp
Accept orice idee pentru a o testa. Multumesc.