>>>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.LocationCode>
>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);>