Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  can't access temp table inside function

Author  Topic  ajmaly Starting Member 11 Posts Posted - 2010-08-24 : 02:07:35 here I am going to define a inline table value function, but I can't access temp table...any other way to do the same? and SP is also not work for me for some reason, I need to this in FUNCTION only CREATE FUNCTION test ( @StrXML XML ) RETURNS TABLE AS RETURN ( CREATE TABLE #t (x XML) INSERT INTO #t(X) SELECT @StrXML DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX) SET @Cols = STUFF( ( SELECT ', MAX(CASE WHEN [Attribute]=' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)'),CHAR(39)) + ' THEN [Attribute_Value] ELSE NULL END) AS ' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)')) + CHAR(13) FROM #t t CROSS APPLY @StrXML.nodes('/PDI_Approval_Message/Transactions/Transaction/Attributes/*')Attributes (attributes) FOR XML PATH(''),TYPE ).value('.','nvarchar(max)'),1,1,'') SET @sql = N' SELECT SourceSystemName, SourceSystemValue, ' + CHAR(13) + @cols + ' FROM( SELECT header.value(''./SourceSystem[1]/@Name'',''VARCHAR(15)'') AS SourceSystemName, header.value(''./SourceSystem[1]/@Value'',''VARCHAR(15)'') AS SourceSystemValue, attributes.value(''local-name(.)'',''VARCHAR(25)'') Attribute, attributes.value(''.'',''VARCHAR(15)'') AS Attribute_Value FROM #t CROSS APPLY x.nodes(''/PDI_Approval_Message/Header'')Header(header) CROSS APPLY x.nodes(''/PDI_Approval_Message/Transactions/Transaction/Attributes/*'')Attributes (attributes) ) AS x GROUP BY SourceSystemName, SourceSystemValue' --PRINT @sql EXEC sp_executesql @sql ) GO and here is the XML, DECLARE @StrXML XML SET @StrXML = '<PDI_Approval_Message> <Header> <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem> </Header> <Transactions> <Transaction> <Attributes> <WF_Regular_Hrs>12</WF_Regular_Hrs> <WF_Overtime_Hrs>3</WF_Overtime_Hrs> </Attributes> </Transaction> </Transactions> </PDI_Approval_Message>' ajmal