May 15, 2009 at 11:35 am
Hi,
I am trying to create a UDF that will open a specified XML file and return a table with the XML files contents all organized to format it.
I can do all this outside of the CREATE FUNCTION clause, but as soon as I try to put it in funciton, I get errors all over the place.
Outside the clause I can use T-SQL like:
CREATE TABLE #xmlImportTable2 ( xml_data XML NOT NULL )
SET @query =
'INSERT INTO #xmlImportTable
SELECT @xmlData = *
FROM OPENROWSET (BULK N''' + @filenameString + ''', SINGLE_BLOB) AS data'
EXEC(@query)
SELECT @xmlData = xml_data FROM #xmlImportTable2
but I can't use the EXEC command within a UDF, so then I tried to define a table variable, and go from there like:
DECLARE @xmlDataTable TABLE (myXMLdata XML)
INSERT INTO @xmlDataTable
SELECT *
FROM OPENROWSET (BULK N''' + @filenameString + ''', SINGLE_BLOB) AS data
but now it tells me I have incorrect syntax near "TABLE" in my declare line.
Does anyone have any ideas on how I might be able to get this to work?
Thanks so much!
May 15, 2009 at 11:58 am
Your error is not related to the DECLARE statement... the following code is a copy of your's and works fine...
CREATE FUNCTION fn_test
(
@filenameString varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @xmlDataTable TABLE (myXMLdata XML)
declare @i int
INSERT INTO @xmlDataTable
SELECT *
FROM OPENROWSET (BULK N''' + @filenameString + ''', SINGLE_BLOB) AS data
set @i =0
return @i
END
GO
May 15, 2009 at 12:05 pm
A better example using a table-valued function would be
CREATE FUNCTION fn_table_test
(
@filenameString varchar(50)
)
RETURNS
@xmlDataTable TABLE
(myXMLdata XML)
AS
BEGIN
-- code here
INSERT INTO @xmlDataTable
SELECT *
FROM OPENROWSET (BULK N''' + @filenameString + ''', SINGLE_BLOB) AS data
RETURN
END
GO
May 15, 2009 at 12:34 pm
I am actually using the table-valued function, but I am using the return table to return the contents of the XML file.
I was able to get the DECLARE statement to work - it worked fine once I had it as a separate DECLARE, rather than grouped in with declarations of other variables within the function.
The only problem I am still having is that I cannot figure out how to parametrize the filename entry for the OPENROWSET command.
My procedure compiles fine with the code:
INSERT INTO @xmlDataTable (myXMLdata)
SELECT *
FROM OPENROWSET (BULK N''' + @filenameString + ''', SINGLE_BLOB) AS data
, however - at run time, I get the error:
Cannot bulk load. The file "' + @filenameString + '" does not exist.
Any ideas on how to have the filename vary?
Thanks again!
May 15, 2009 at 1:26 pm
Hi
The problem does not depend on your CREATE FUNCTION but on the fact that you cannot call OPENROWSET with variables as parameter.
This also doesn't work (outside of a function):
DECLARE @filenameString varchar(50)
SELECT *
FROM OPENROWSET
(
BULK ''' + @filenameString + ''', SINGLE_BLOB
) AS data
I think the only way would be a procedure.
Greets
Flo
May 15, 2009 at 2:01 pm
You need to use dynamic sql to accomplish what you are trying to do. I don't know if you can use INSERT ... EXEC(...) to populate a table variable and I think it will be prohibited in a UDF as it won't allow code that may produce side effect changes and with dynamic sql you won't know if it has side effects until it is run (cn change from execution to execution).
May 15, 2009 at 2:03 pm
Unless I'm wrong, you can't use dynamic SQL from within a function? Please tell me I'm wrong, in which case this might be a lot easier? 😀
May 15, 2009 at 2:07 pm
You are correct, you cannot :pinch:
You can create a dynamic SQL statement, but you cannot execute
May 15, 2009 at 2:14 pm
Yo can use a stored proc in place of the UDF....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply