Business scenario: We have an XML file (HIPAA 837) saved as a XML data column in a table and we need to go through each row and get different information on different nodes and output to a table. The real reason of this design lies in with how the claim system is designed, which I will not try to explain here.
Approach:
- Using Common Table Expression (CTE) to get all the necessary info so that the data can be joined later.
- Use ROW_NUMBER() to make sure the information comes from the correct line.
Journey:
- Had to use Cross Apply because the value is stored in the attribute instead of the node
- Could not use Cross Apply for all fields in the same select because rows get duplicated
- Could not use cursor because [xmldatatype].query() only takes string as parameter.
If someone can read up on this and make suggestions, I'd greatly appreciate it, because I really didn't like the fact that I had to go into the same table 5 times just to get the 5 different columns
WITH TmpXMLNode
(tmpcol
, TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid)
AS
(
SELECT CAST(REPLACE(REPLACE(REPLACE(CAST(xmldata AS varchar(max)),'>','>'),'<','<'),
'xmlns="http://schemas.qcsi.com/Messages/Hipaa837"','') AS xml) AS tmpcol
, processlogdetailid
, processstageid
, processlogid
, referenceid
FROM
ProcessLogDetail
WHERE
ProcessLogTypeID = 'BIZTALK-837'
AND xmldata IS NOT NULL
)
,TmpXMLNodeSD
(TmpXMLSD_processlogdetailid
, TmpXMLSD_processstageid
, TmpXMLSD_processlogid
, TmpXMLSD_referenceid
, SDRowCnt
, SDtmpcol
)
AS
(
SELECT
TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid
, ROW_NUMBER() OVER (PARTITION BY TmpXML_processlogdetailid ORDER BY TmpXML_processlogdetailid) AS RowCnt
, nref.value('data(@TS837Q1_2400_DTP03__ServiceDate)', 'varchar(255)') AS ServiceDateXML
FROM
TmpXMLNode
CROSS APPLY
TmpXMLNode.tmpcol.nodes('/import837Request//HipaaMessage//X12_4010_837//TS837Q1_2000A//TS837Q1_2000B//TS837Q1_2300//TS837Q1_2400//TS837Q1_2400_DTP_DateServiceDate') AS R(nref)
)
,TmpXMLNodePC
(TmpXMLPC_processlogdetailid
, TmpXMLPC_processstageid
, TmpXMLPC_processlogid
, TmpXMLPC_referenceid
, PCRowCnt
, PCtmpcol
)
AS
(
SELECT
TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid
, ROW_NUMBER() OVER (PARTITION BY TmpXML_processlogdetailid ORDER BY TmpXML_processlogdetailid) AS RowCnt
, nref.value('data(@TS837Q1_2400_SV101_C00302U949_ProcedureCode)', 'varchar(255)') AS ProcCodeXML
FROM
TmpXMLNode
CROSS APPLY
TmpXMLNode.tmpcol.nodes('/import837Request//HipaaMessage//X12_4010_837//TS837Q1_2000A//TS837Q1_2000B//TS837Q1_2300//TS837Q1_2400//TS837Q1_2400_SV1_ProfessionalService//TS837Q1_2400_SV101_C003U947') AS R(nref)
)
,TmpXMLNodeDC
(TmpXMLDC_processlogdetailid
, TmpXMLDC_processstageid
, TmpXMLDC_processlogid
, TmpXMLDC_referenceid
, DCRowCnt
, DCtmpcol
)
AS
(
SELECT
TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid
, ROW_NUMBER() OVER (PARTITION BY TmpXML_processlogdetailid ORDER BY TmpXML_processlogdetailid) AS RowCnt
, nref.value('data(@TS837Q1_2400_SV107_C00401U956_DiagnosisCodePointer)', 'varchar(255)') AS DiagCodeXML
FROM
TmpXMLNode
CROSS APPLY
TmpXMLNode.tmpcol.nodes('/import837Request//HipaaMessage//X12_4010_837//TS837Q1_2000A//TS837Q1_2000B//TS837Q1_2300//TS837Q1_2400//TS837Q1_2400_SV1_ProfessionalService//TS837Q1_2400_SV107_C004U955') AS R(nref)
)
,TmpXMLNodeCHA
(TmpXMLCHA_processlogdetailid
, TmpXMLCHA_processstageid
, TmpXMLCHA_processlogid
, TmpXMLCHA_referenceid
, CHARowCnt
, CHAtmpcol
)
AS
(
SELECT
TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid
, ROW_NUMBER() OVER (PARTITION BY TmpXML_processlogdetailid ORDER BY TmpXML_processlogdetailid) AS RowCnt
, nref.value('data(@TS837Q1_2400_SV102__LineItemChargeAmount)', 'varchar(255)') AS ChargeXML
FROM
TmpXMLNode
CROSS APPLY
TmpXMLNode.tmpcol.nodes('/import837Request//HipaaMessage//X12_4010_837//TS837Q1_2000A//TS837Q1_2000B//TS837Q1_2300//TS837Q1_2400//TS837Q1_2400_SV1_ProfessionalService') AS R(nref)
)
,TmpXMLNodeUN
(TmpXMLUN_processlogdetailid
, TmpXMLUN_processstageid
, TmpXMLUN_processlogid
, TmpXMLUN_referenceid
, UNRowCnt
, UNtmpcol
)
AS
(
SELECT
TmpXML_processlogdetailid
, TmpXML_processstageid
, TmpXML_processlogid
, TmpXML_referenceid
, ROW_NUMBER() OVER (PARTITION BY TmpXML_processlogdetailid ORDER BY TmpXML_processlogdetailid) AS RowCnt
, nref.value('data(@TS837Q1_2400_SV104__ServiceUnitCount)', 'varchar(255)') AS UnitXML
FROM
TmpXMLNode
CROSS APPLY
TmpXMLNode.tmpcol.nodes('/import837Request//HipaaMessage//X12_4010_837//TS837Q1_2000A//TS837Q1_2000B//TS837Q1_2300//TS837Q1_2400//TS837Q1_2400_SV1_ProfessionalService') AS R(nref)
)
SELECT TOP 100
SDtmpcol AS ServiceDate
, PCtmpcol AS ProcCode
, DCtmpcol AS DiagCode
, CHAtmpcol AS Charge
, UNtmpcol AS Unit
, processid = ''
, referenceid claimid
, '1' claimline
,GetDate() CreateDate
FROM
(SELECT TmpXMLSD_processlogdetailid AS TmpXML_processlogdetailid
, TmpXMLSD_processstageid AS TmpXML_processstageid
, TmpXMLSD_processlogid AS TmpXML_processlogid
, TmpXMLSD_referenceid AS TmpXML_referenceid
, SDtmpcol
, PCtmpcol
, DCtmpcol
, CHAtmpcol
, UNtmpcol
FROM TmpXMLNodeSD AS SD
LEFT OUTER JOIN TmpXMLNodePC AS PC
ON SD.TmpXMLSD_processlogdetailid = PC.TmpXMLPC_processlogdetailid
AND SD.TmpXMLSD_processstageid = PC.TmpXMLPC_processstageid
AND SD.TmpXMLSD_processlogid = PC.TmpXMLPC_processlogid
AND SD.TmpXMLSD_referenceid = PC.TmpXMLPC_referenceid
AND SD.SDRowCnt = PC.PCRowCnt
LEFT OUTER JOIN TmpXMLNodeDC AS DC
ON SD.TmpXMLSD_processlogdetailid = DC.TmpXMLDC_processlogdetailid
AND SD.TmpXMLSD_processstageid = DC.TmpXMLDC_processstageid
AND SD.TmpXMLSD_processlogid = DC.TmpXMLDC_processlogid
AND SD.TmpXMLSD_referenceid = DC.TmpXMLDC_referenceid
AND SD.SDRowCnt = DC.DCRowCnt
LEFT OUTER JOIN TmpXMLNodeCHA AS CHA
ON SD.TmpXMLSD_processlogdetailid = CHA.TmpXMLCHA_processlogdetailid
AND SD.TmpXMLSD_processstageid = CHA.TmpXMLCHA_processstageid
AND SD.TmpXMLSD_processlogid = CHA.TmpXMLCHA_processlogid
AND SD.TmpXMLSD_referenceid = CHA.TmpXMLCHA_referenceid
AND SD.SDRowCnt = CHA.CHARowCnt
LEFT OUTER JOIN TmpXMLNodeUN AS UN
ON SD.TmpXMLSD_processlogdetailid = UN.TmpXMLUN_processlogdetailid
AND SD.TmpXMLSD_processstageid = UN.TmpXMLUN_processstageid
AND SD.TmpXMLSD_processlogid = UN.TmpXMLUN_processlogid
AND SD.TmpXMLSD_referenceid = UN.TmpXMLUN_referenceid
AND SD.SDRowCnt = UN.UNRowCnt
) AS TmpXMLResult
INNER JOIN ProcessLogDetail
ON TmpXMLResult.TmpXML_processlogdetailid = ProcessLogDetail.processlogdetailid
AND TmpXMLResult.TmpXML_processstageid = ProcessLogDetail.processstageid
AND TmpXMLResult.TmpXML_processlogid = ProcessLogDetail.processlogid
AND TmpXMLResult.TmpXML_referenceid = ProcessLogDetail.referenceid
WHERE ProcessLogDetail.ProcessLogTypeID = 'BIZTALK-837'
AND ProcessLogDetail.xmldata IS NOT NULL