Passing a Table to A Stored Procedure

  • Hi Jacob,

    I was really excited after reading your article and started to try implementing the xml stuff on my sql server 2000 query analyzer.

    I could select the table data as xml using the select statements...but when i try to declare a variable of type xml it gave me an error. Here is a code snippet of that:

    DECLARE @x XML

    This is the error message that i got:

    Column or parameter #-1: Cannot find data type XML.

    Parameter '@x' has an invalid data type.

    I have looked on many forums to solve this issue, but cannot. Can you throw some light on this?

  • SQL server 2000 doesn't support the XML data type. It was introduced with SQL server 2005.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • SQL Server 2000 does not support the XML data type. If you want to work with parameters in XML format, you can still pass them as TEXT and use OPENXML() to query on them.

    Have a look at

    http://www.sqlservercentral.com/articles/Miscellaneous/2908/

    http://www.sqlservercentral.com/articles/Miscellaneous/2909/

    http://www.sqlservercentral.com/articles/Miscellaneous/2911/

    .

  • the statement :

    declare @x xml

    is not working for me in sql server 2005 too..Is there any configuration I need to do specifically to enable that.

  • I am not aware of any such setting. XML is a built-in data type like INT or VARCHAR. So there should not be a special setting to enable it.

    try SELECT @@version and see what you are getting.

    .

  • Jacob,

    Tell me.. how to take the reference of last record updated in my destiny, an Oracle Datbase Table and copy the next record from my sql table???

    please

    Vasu

  • Vasudev Tantry (12/18/2007)


    Jacob,

    Tell me.. how to take the reference of last record updated in my destiny, an Oracle Datbase Table and copy the next record from my sql table???

    please

    Vasu

    .

  • Hi,

    Hold the Bus. I think I figured it out. I will sed you an update when I am done to see if there is a better way to accomplish this.

    Thanks,

    Alan

  • SQL Server 2008 has better solution. Now we can declare table valued parameter.

    http://technet.microsoft.com/en-us/library/bb510489(SQL.100).aspx

  • Hi,

    Yes I am aware of this. Unfortunatly I am using SQL2K and SQL2005. My soultion does work well for it's intended use. Not Ideal for huge amounts of data.

    Thanks...

  • Nice Article with clear explanation

    I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML

    as per my knowledge xml data has limit of 8000 char.

    How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

    Sachin Thamke
    Mumbai, India
  • Nice Article with clear explanation

    I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML

    as per my knowledge xml data has limit of 8000 char.

    How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

    Sachin Thamke
    Mumbai, India
  • Have a look here: very interesting article with several ways how to pass arrays/tables to SP. Ofcourse, you don't REALLY pass a table, but his solutions and examples have helped me to solve a similar problem.

    Greetz,
    Hans Brouwer

  • sachinthamke (6/19/2008)


    Nice Article with clear explanation

    I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML

    as per my knowledge xml data has limit of 8000 char.

    How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

    The XML data type can store upto 2 Gb of data.

    .

  • Hello,

    not sure if I have the full picture! Why would you like to pass a full table to a procedure, why not pass a tablename instead and open the table in the store procedure or function?

    Thanks

    Jose

Viewing 15 posts - 76 through 90 (of 100 total)

You must be logged in to reply to this topic. Login to reply