SQL Server Performance

table variable

Discussion in 'SQL Server 2005 Integration Services' started by arkiboys, Oct 8, 2008.

  1. arkiboys Member

    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.
  2. satya Moderator

    Bear in mind such a process might hurt the performance and can stress the TEMPDB as you are playing with table variables, so I wanted to know what is the issue in having a permanent table here.
  3. arkiboys Member

    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.
  4. Adriaan New Member

    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.
  5. arkiboys Member

    Do you mean pass the xml file name to the stored procedure?
  6. Adriaan New Member

    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.
  7. arkiboys Member

    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?
  8. Adriaan New Member

    Sorry, my only experience with XML and SQL is faking XML output from SQL 2000.[;)]
  9. arkiboys Member

  10. Adriaan New Member

    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 ...
  11. arkiboys Member

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

    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?
  13. arkiboys Member

    I can pass the filename but not the content of the file.
  14. Adriaan New Member

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

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

    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.
    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.
  17. arkiboys Member

    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.
  18. Adriaan New Member

Share This Page