>>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 ) ;>>
inner join openxml(@docHandle, N'/ROOT/transactions') > WITH ( > id int, > LocationCode char(2) > ) t > ON c.id = t.id and c.LocationCode = t.LocationCodeYou 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.