Hi Stephen,

Try

SELECT *
        INTO    #PrimaryKeys
        FROM    OPENXML ( @idoc , '/*/*' )
        WITH    ( pk int )

BTW, you don't have to create a temp table. The OPENXML() can be used directly in your query.

SELECT * FROM dbo.Company co
	WHERE co.Company_id IN (
		SELECT pk
	        FROM    OPENXML ( @idoc , '/*/*' )
    	    WITH    ( pk int ))
-- Or
SELECT * FROM dbo.Company co
	JOIN OPENXML ( @idoc , '/*/*' ) WITH    ( pk int )) xl
		ON xl.pk = co.Company_id

>I think this method of passing a SQL parm object from FoxPro to SQL is pretty cool! Basically, you use CURSORTOXML on the fox side, and use the code below to create a table on the SQL side. I am planning on passing two tables. The first is a "settings" table, where settings of different types are in columns, and there is just one row. The second is a "keys" table, and will have multiple rows of keys for joining with, and perhaps a column that tells what the keys are, or groups them in some way. This actually works now, but I have to make it work with OUR namespace, instead of none.
>
>The problem is that we're trying to use a fox CURSORTOXML to send a "parameter-object" to SQL, and it works as long as you don't set-up the last parm in CURSORTOXML:
>
> CURSORTOXML([SqlParm],[lxData],1,2,0,[1],[],[])
>
>but my boss says that we must use it with the namespace:
>
> CURSORTOXML([SqlParm],[lxData],1,2,0,[1],[],[http://www.ourcompany.com])
>
>Embedded below is the sql stored procedure to which we're sending the parm object, and some fox code which calls it. You have to add code that connects to the database, and change the stored procedure so it pulls from a table in your database, and then hardcode some primary keys going into SqlParm in the fox program.
>
>Has anybody done this or know what the problem is?
>Any help would be much appreciated.
>Thanks in advance!
>
>P.S. Will Sanders (on VFUG mail list) said to try it with http://localhost to see if that would work, but it didn't.
>

>*-----------------------------------------------------------------------
>* begin fox prg
>*-----------------------------------------------------------------------
>
>CREATE CURSOR SqlParm ( PK I )
>INSERT INTO SqlParm ( PK ) VALUES ( 95 )
>INSERT INTO SqlParm ( PK ) VALUES ( 97 )
>INSERT INTO SqlParm ( PK ) VALUES ( 103 )
>
>LOCAL lxData
>
>SET STEP ON
>
>*########################################################################
>*Try running this program with each of these lines un
>*########################################################################
>* This works with SQL
>CURSORTOXML([SqlParm],[lxData],1,2,0,[1],[],[])
>
>* But this does not work with SQL
>*CURSORTOXML([SqlParm],[lxData],1,2,0,[1],[],[http://www.ourcompany.com])
>*########################################################################
>
>*_CLIPTEXT = lxData
>
>USE IN SqlParm
>
>* PUBLIC lnHandle
>*       lnHandle = SQLSTRINGCONNECT()
>? SQLEXEC(lnHandle,[EXEC dbo.zzzTest ?lxData],[cCompanies])
>
>*-----------------------------------------------------------------------
>* end fox prg
>*-----------------------------------------------------------------------
>
>*-----------------------------------------------------------------------
>* begin stored procedure Currently using SQL Server 2000.
>*-----------------------------------------------------------------------
>
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzzTest]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>
>drop procedure [dbo].[zzzTest]
>GO
>
>CREATE PROCEDURE dbo.zzzTest @lxParmObj varchar(8000) AS
>DECLARE @idoc int
>
>-- Create an internal representation of the XML document
>
>EXEC sp_xml_preparedocument @idoc OUTPUT, @lxParmObj
>
>-- Execute a SELECT statement using OPENXML rowset provider.
>-- Put the set of records into a temp table called #PrimaryKeys.
>-- The '/VFPData' part never changes ( when using FoxPro CURSORTOXML )
>-- 'sqlparm' is the LOWERCASE name of the fox cursor used to create the xml.
>-- 'pk' is the LOWERCASE name of the column in the fox cursor used to create the xml.
>
>SELECT *
>        INTO    #PrimaryKeys
>        FROM    OPENXML ( @idoc , '/VFPData/sqlparm' , 2 )
>        WITH    ( pk int )
>
>-- Remove the internal representation of the XML document
>
>EXEC sp_xml_removedocument @idoc
>
>-- Now, use the data from the XML to select specific companies
>-- This table is returned to the calling program
>
>SELECT * FROM dbo.Company co
>        WHERE co.Company_id IN
>                (SELECT PK FROM #PrimaryKeys)
>
>-- Fastidiously Drop our temp table :-)
>
>DROP TABLE #PrimaryKeys
>GO
>
>GRANT EXECUTE ON [dbo].[zzzTest] to [GComAppRole]
>GO
>
>*-----------------------------------------------------------------------
>* end stored procedure
>*-----------------------------------------------------------------------