Can someone please help me in understanding the below query. I'm using SQL 2012.
I could not exactly understand why the XML were being used in this query and how does CROSS APPLY works, but my doubts were cleared by Steven Hibble, thanks to him.
(SELECT CAST ('<B>' + REPLACE(GLO_PROD_ID, ',', '</B><B>') + '</B>' AS XML) AS P_ITEM_ID,
FROM BUNDLE_LOGIC U
) AS BL CROSS APPLY P_ITEM_ID.nodes ('/B') AS Split(a)
JOIN AB_DS_GLOBAL_LOCAL_COMP T2 ON T2.GLO_PROD_ID = Split.a.value('.', 'NVARCHAR(100)')
Your query appears incomplete. There's probably a
SELECT... before the first parenthesis.
In any case, it looks like you have a field in
BUNDLE_LOGIC that is essentially a CSV -
GLO_PROD_ID contains many values per row that look something like
1,2,3 or maybe
abc,def,ghi. This code essentially splits that field into multiple rows - so that each individual value in
GLO_PROD_ID can be
AB_DS_GLOBAL_LOCAL_COMP. It works like this:
CROSS APPLYto use the nodes method to separate each B to its own row
AB_DS_GLOBAL_LOCAL_COMPon the individual
Here's a mocked up example:
DECLARE @Table1 TABLE ([ID] INT IDENTITY(1, 1) , [CSVString] VARCHAR(1000)); INSERT INTO @Table1 VALUES ('val1,val2,val3,val4'), ('val1,val2,val5,val6'); DECLARE @Table2 TABLE ([ValueCode] VARCHAR(10) , [ValueDescription] VARCHAR(100)); INSERT INTO @Table1 VALUES ('val1,val2,val3,val4') , ('val1,val2,val5,val6'); INSERT INTO @Table2 VALUES ('val1', 'Description 1') , ('val2', 'Description 2') , ('val3', 'Description 3') , ('val4', 'Description 4') , ('val5', 'Description 5') , ('val6', 'Description 6'); -- Similar to BUNDLE_LOGIC SELECT * FROM @Table1; -- Similar to AB_DS_GLOBAL_LOCAL_COMP SELECT * FROM @Table2; -- JOIN them together by splitting the CSV using XML SELECT [tab1].[ID] , [tab2].[ValueCode] , [tab2].[ValueDescription] FROM (SELECT [ID] , CAST('<B>'+REPLACE([CSVString], ',', '</B><B>')+'</B>' AS XML) AS [CSVToXML] FROM @Table1) [tab1] CROSS APPLY [CSVToXML].nodes('/B') AS [Split]([a]) JOIN @Table2 [tab2] ON [tab2].[ValueCode] = [Split].[a].value('.', 'VARCHAR(100)');
P.S. You may want to consider storing each value as a row to begin with. It takes more storage space (and the code populating
BUNDLE_LOGIC would have to be altered), but it would be easier to understand and usually quicker. If that's not an option, Jeff Moden has a great CSV splitting function.