T-SQL extract multiple child node attribute data out of an XML datatype.

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: 

  1. Using Common Table Expression (CTE) to get all the necessary info so that the data can be joined later.
  2. Use ROW_NUMBER() to make sure the information comes from the correct line.

Journey:

  1. Had to use Cross Apply because the value is stored in the attribute instead of the node
  2. Could not use Cross Apply for all fields in the same select because rows get duplicated
  3. 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)),'&gt;','>'),'&lt;','<'),
  '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
 

Print | posted on Monday, November 23, 2009 4:48 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski