CROSS APPLY over multiple columns with related data

_{Friday, July 22, 2016 4:37 PM}

I am trying to create an audit report from CRM 2011 data.  I am pulling all of the info I need, but I'm having trouble doing a CROSS APPLY while maintaining the positional relationship.  What I mean is, values 447,446,33 are related to the corresponding ChangeData 1~0~SOME RELATED STRING DATA (see example below).  

When I use CROSS APPLY on both columns, I am getting "duplicate" rows

What I have in the Raw data

What I get when I try to use CROSS APPLY

What I need

This isn't pretty because I'm working through this issue, but here is the code I'm using

declare   @Date1 datetime
        , @Date2 datetime
        , @ObjectTypeCode int
        , @ObjectID varchar(40)

set @Date1 = '07/15/2016 00:00:00.000'
set @Date2 = '07/25/2016 23:59:00.000'
set @ObjectTypeCode = 10185 -- IPM Equipment Master
set @ObjectID = '2F0992DC-95AB-E311-B536-005056B51407'

declare @AttributeMaskTable TABLE 
        (TransactionID varchar(40), AttributeMask VARCHAR(8000)--,  ChangeData VARCHAR(8000)
        )
        insert into @AttributeMaskTable select TransactionID, attributemask from Audit
where CreatedOn between @Date1 and @Date2
and ObjectTypeCode = @ObjectTypeCode
and AttributeMask >''
and OBJECTID = @ObjectID

declare @ChangeDataTable TABLE 
        (TransactionID varchar(40), ChangeData VARCHAR(8000)
        )
        insert into @ChangeDataTable select TransactionID, ChangeData from Audit
where CreatedOn between @Date1 and @Date2
and ObjectTypeCode = @ObjectTypeCode
and AttributeMask >''
and OBJECTID = @ObjectID

; with AttributeMask AS(
SELECT TransactionID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS AttributeMask
FROM
(
SELECT TransactionID,CAST('<XMLRoot><RowData>' + REPLACE(AttributeMask,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @AttributeMaskTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
)

, ChangeData AS(
SELECT TransactionID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS ChangeData
FROM
(
SELECT TransactionID,CAST('<XMLRoot><RowData>' + REPLACE(ChangeData,'~','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @ChangeDataTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
)


select distinct a.TransactionID ,AttributeMask  ,ChangeData
from AttributeMask a
left join ChangeData b
on a.TransactionID = b.TransactionID
where AttributeMask > ''
and a.TransactionID = '50B6B544-674F-E611-8DD5-005056B51407'

I have seen a few situations such as this one: https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ where I might need to use a function.  I'm going to chase this rabbit until I get some feedback otherwise.  Any help is appreciated.

_{Sunday, July 24, 2016 10:05 PM}

CROSS APPLY builds a Cartesian product. Thus you need to modify you predicates in the CROSS APPLY subquery. So you need a splt function, which also returns the position.

E.g.

CREATE FUNCTION [dbo].[fnSplitString]
    (
      @string NVARCHAR(MAX) ,
      @delimiter CHAR(1)
    )
RETURNS @output TABLE
    (
      position INT IDENTITY(1, 1) ,
      splitdata NVARCHAR(MAX)
    )
    BEGIN 
        DECLARE @start INT ,
            @end INT 
        SELECT  @start = 1 ,
                @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 
            BEGIN 
                IF @end = 0 
                    SET @end = LEN(@string) + 1
       
                INSERT  INTO @output
                        ( splitdata
                        )
                VALUES  ( SUBSTRING(@string, @start, @end - @start)
                        ) 
                SET @start = @end + 1 
                SET @end = CHARINDEX(@delimiter, @string, @start)
        
            END 
        RETURN 
    END

with

DECLARE @Sample TABLE
(
    TransactionID UNIQUEIDENTIFIER,
    AttributeMask VARCHAR(255),
    ChangeData VARCHAR(255)
);
 
INSERT INTO @Sample
VALUES  ( '50B6B544-674F-E611-8DD5-005056B51407', ',447,446,33,', '1~0~SOME RELATED STRING DATA' );

SELECT  *
FROM    @Sample S
    CROSS APPLY [fnSplitString](SUBSTRING(S.AttributeMask, 2, 1024), ',') A
    CROSS APPLY [fnSplitString](S.ChangeData, '~') T
WHERE   A.position = T.position;
 

You just need to get rid of that leading comma.