>>Hi Frank,
>>
>>I've checked one of the SPs and in that SP we're passing info like this
>>
>>Id,Quantity, DateTime;Id,Quantity,DateTime as varchar(max) string. For that we're using the following:
>>
>>

>>with  cteSource ( Data )
>>              as ( select  cast('<v><i>' + replace(replace(@cMbRedemptions, ';', '</i></v><v><i>'), ',', '</i><i>') +
>>                           '</i></v>' as xml) as Data
>>                 )
>>         insert   into #tblRedemptions
>>                  ( BenefitID,
>>                    Quantity,
>>                    Date_Time )
>>                  select   v.value('i[1]', 'INT') as BenefitID,
>>                           v.value('i[2]', 'INT') as Quantity,
>>                           v.value('i[3]', 'datetime') as Date_Time
>>                  from     cteSource as s
>>                           cross apply Data.nodes('v') as n ( v ) ;

>>
>>I can check other samples as I know we do use XML in a few cases, I just need to recall where.
>
>Thanks Naomi,
>
>I think I got it. Code like this works:
>
>

inner join openxml(@docHandle, N'/ROOT/transactions') 
>		WITH (
>		id int,
>		LocationCode char(2)
>		) t
>	ON c.id = t.id and c.LocationCode = t.LocationCode

You can avoid using openxml. I found another example where I pass XML:

CREATE PROCEDURE [dbo].[siriussp_ImportCreditNumbers] ( 
                @vcStr VARCHAR(MAX)) 
--============================================== 
AS 
  BEGIN 
    SET NOCOUNT  ON; 
     
    DECLARE  @x XML 
     
    SET @x = CONVERT(XML,@vcStr) 
     
    IF OBJECT_ID('TempDB..#csrCardNos','U') IS NOT NULL 
      DROP TABLE #csrCardNos 
     
    CREATE TABLE #csrCardNos ( 
      num_assign CHAR(17) collate SQL_Latin1_General_CP1_CI_AS, 
      num_source INT,
      pin        CHAR(4)  collate SQL_Latin1_General_CP1_CI_AS) 
     
    INSERT INTO #csrCardNos 
               (num_assign, 
                num_source,
                pin) 
    SELECT T.n.value('(num_assign/text())[1]','char(17)') AS [num_Assign], 
           T.n.value('(num_source/text())[1]','int')      AS [num_Source],
           T.n.value('(pin/text())[1]','char(4)')         AS [pin]
    FROM   @x.nodes('/VFPData/cardnos') AS T(n);

I can show you VFP code if needed.