3 added 6 characters in body
source | link

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I<I></I> for the XML arrays constructed within the WITH XMLTaggedList statmentCTE statement. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (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')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I for the XML arrays constructed within the WITH XMLTaggedList statment. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (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')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element <I></I> for the XML arrays constructed within the XMLTaggedList CTE statement. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (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')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)
2 added 1277 characters in body
source | link

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I for the XML arrays constructed within the WITH XMLTaggedList statment. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (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')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I for the XML arrays constructed within the WITH XMLTaggedList statment. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I for the XML arrays constructed within the WITH XMLTaggedList statment. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (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')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)
1
source | link

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element I for the XML arrays constructed within the WITH XMLTaggedList statment. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.