Newbie Newbie - 3 months ago 15
SQL Question

CROSS APPLY in SQL

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,
U.COUNTRY_ID,
U.COUNTRY_DESC,
U.COMPET_SERIES_ID,
U.COMPET_SERIES_NAME,
U.COMPETITOR_ID,
U.COMPETITOR_NAME,
U.GLO_PROD_ID,
U.GLO_PROD_NAME

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)')


Thanks.

Answer

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 JOINed to AB_DS_GLOBAL_LOCAL_COMP. It works like this:

  1. Replace all the commas with a closing and opening XML tag ()
  2. Book end the whole thing with an opening tag () at the front and a closing tag at the end (<\B>)
  3. Convert that to XML
  4. Use CROSS APPLY to use the nodes method to separate each B to its own row
  5. Use the value method to extract the value (converting it to a SQL Server data type - in this case NVARCHAR(100))
  6. JOIN to AB_DS_GLOBAL_LOCAL_COMP on the individual values

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.

Comments