I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is doing the uncompacting. Here Is the SQL Fiddle for the below code
create table dimDiagnosisGroup (dxGroupKey int, dxCodeList nvarchar(1024)) insert into dimDiagnosisGroup values (1,'042, 070.70, 722.10'), (2,'042, 070.70, 780.52, 496, 716.90, 581.9'), (3,'042, 070.70, 782.1, V58.69'), (4,'042, 070.70, 782.3, V58.69') WITH XMLTaggedList AS ( SELECT dxGroupKey, CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML) AS Diagnosis_Code_List FROM dimDiagnosisGroup ) SELECT dxGroupKey, ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2 FROM XMLTaggedList CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)
I understand the XMLTaggedList part fine. What I am not understanding is the Cross Apply to the ExtractedDiagnosisList(X), then the ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)').
When I hover over ExtractedDiagnosisList in the select statement, SSMS says it is a derived table. However, it kind of looks like a function to me. I am not understanding how Diagnosis_Code.List gets the .nodes('//I') function. Finally, the ExtractedDiagnosisList.X.value section just looks foreign to me, in SQL. It looks like syntax from a language like C#.