table variable

How is it possible to pass the xml content of a file from xml source to a table variable?
I would then like to query the table variable and pass the required data into other tables.
I do not want to create a permanent table to hold the initial xml contents.
Thanks

There is no issue at present infact I am using a permanent table at present.
I just would like to know if there is a way to have a temp table instead and so to transfer the data from xml source into it.
Thanks

SQL Server will create the table variable in tempdb only if it expects the total amount of data to be above a certain threshold, otherwise it will create it in memory only.
Is your problem that the jobs that you create in IS do not support table variables? In that case, you would need to create a stored procedure that you can call from the IS job, and feed the path to the XML file as a parameter, then handle all manipulations in the stored procedure.

Do you mean pass the xml file name to the stored procedure?

Yes, including the full path. If IS is running under different account than SQL Server, then doiuble-check if path is accessible to the account under which SQL Server is running.

If I can pass the whole xml content of the xml file to a stored procedure as a variable then I think my issue is solved.
Do you know how this is done please?

Sorry, my only experience with XML and SQL is faking XML output from SQL 2000.[]

[]

Meanwhile, I notice your other thread saying you are already passing the file path to a stored procedure. There should be plenty of standard T-SQL syntax to access and manipulate XML files.
One thing that used to be a big problem in older SQL versions (7.0 and 2000) was that a lot of file-related functions could only be called with a constant expression, and not with a variable. In that case, you needed to resort to dynamic SQL to be able to call those functions with variable parameters. I do hope they cleared that up in 2005, as things would get messy --- like I said, I've only ever faked XML output, so I do know exactly how messy ...

I believe my other thread is ASKING how to pass the xml content of an xml file into a stored procedure.
Thanks

It does, but it also SAYS you are already passing the file name (or file path) to the procedure. Why not solve it inside the procedure?

I can pass the filename but not the content of the file.

Yes, and inside the stored procedure you can use the XML-related syntax of T-SQL to access the file.

I think you are referring to something like openrowset, right?
My question is not that.

Well, your original question was how you could get the contents of an XML file into a table variable. Inside a stored procedure, you can use the following syntax:
DECLARE @table TABLE (column_definitions)
INSERT INTO @table
SELECT column_list
FROM OPENROWSET (.................................)
... and from there on you can query against @table --- at least inside the stored procedure.
[EDIT]
And to show you just how little I know about XML, I forgot all about the XML data type - you could have a table with just one column of the XML data type.

Thanks for your time but as mentioned before I would like to pass the xml content to a stored procedure variable and do not want to use openrowset.
Thanks