sql server query xml related nodes

Question

In the T-SQL sample code below I am trying to query related pieces of data that are in different nodes in the xml, but I can't figure out how to do that. For example, the LX01_AssignedNumber and the C00302_ProcedureCode values need to be pulled together for the same record. The result should look like this.

CLAIM_SOURCE_ID ITEM_NUMBER HCPCS_LINE_CODE

16202E123456    1           99203

16202E123456    2           96372

Can someone help me?

    USE [tempdb];
    GO

    DECLARE @XML XML =
    N'<ns1:X12EnrichedMessage xmlns:ns1="http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML">
      <TransactionSet>
        <!-- ProcessLogID=PLG0007182226  ;ProcessLogDetailID=PLG0007182968  ;EnvID=1;RetryCount=1 -->
        <ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
          <ns0:TS837_2000A_Loop xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
            <ns0:TS837_2000B_Loop xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
              <ns0:TS837_2300_Loop xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
                <ns0:TS837_2400_Loop>
                  <ns0:LX_ServiceLineNumber>
                    <LX01_AssignedNumber>1</LX01_AssignedNumber>
                  </ns0:LX_ServiceLineNumber>
                  <ns0:SV1_ProfessionalService>
                    <ns0:C003_CompositeMedicalProcedureIdentifier>
                      <C00301_ProductorServiceIDQualifier>HC</C00301_ProductorServiceIDQualifier>
                      <C00302_ProcedureCode>99203</C00302_ProcedureCode>
                      <C00303_ProcedureModifier>25</C00303_ProcedureModifier>
                      <C00307_Description>NO DESCRIPTION</C00307_Description>
                    </ns0:C003_CompositeMedicalProcedureIdentifier>
                    <SV102_LineItemChargeAmount>167.82</SV102_LineItemChargeAmount>
                    <SV103_UnitorBasisforMeasurementCode>UN</SV103_UnitorBasisforMeasurementCode>
                    <SV104_ServiceUnitCount>1</SV104_ServiceUnitCount>
                    <ns0:C004_CompositeDiagnosisCodePointer>
                      <C00401_DiagnosisCodePointer>1</C00401_DiagnosisCodePointer>
                    </ns0:C004_CompositeDiagnosisCodePointer>
                  </ns0:SV1_ProfessionalService>
                  <ns0:DTP_SubLoop_2>
                    <ns0:DTP_Date_ServiceDate>
                      <DTP01_DateTimeQualifier>472</DTP01_DateTimeQualifier>
                      <DTP02_DateTimePeriodFormatQualifier>RD8</DTP02_DateTimePeriodFormatQualifier>
                      <DTP03_ServiceDate>20160627-20160627</DTP03_ServiceDate>
                    </ns0:DTP_Date_ServiceDate>
                  </ns0:DTP_SubLoop_2>
                </ns0:TS837_2400_Loop>
                <ns0:TS837_2400_Loop>
                  <ns0:LX_ServiceLineNumber>
                    <LX01_AssignedNumber>2</LX01_AssignedNumber>
                  </ns0:LX_ServiceLineNumber>
                  <ns0:SV1_ProfessionalService>
                    <ns0:C003_CompositeMedicalProcedureIdentifier>
                      <C00301_ProductorServiceIDQualifier>HC</C00301_ProductorServiceIDQualifier>
                      <C00302_ProcedureCode>96372</C00302_ProcedureCode>
                      <C00307_Description>NO DESCRIPTION</C00307_Description>
                    </ns0:C003_CompositeMedicalProcedureIdentifier>
                    <SV102_LineItemChargeAmount>82.56</SV102_LineItemChargeAmount>
                    <SV103_UnitorBasisforMeasurementCode>UN</SV103_UnitorBasisforMeasurementCode>
                    <SV104_ServiceUnitCount>2</SV104_ServiceUnitCount>
                    <ns0:C004_CompositeDiagnosisCodePointer>
                      <C00401_DiagnosisCodePointer>2</C00401_DiagnosisCodePointer>
                    </ns0:C004_CompositeDiagnosisCodePointer>
                  </ns0:SV1_ProfessionalService>
                  <ns0:DTP_SubLoop_2>
                    <ns0:DTP_Date_ServiceDate>
                      <DTP01_DateTimeQualifier>472</DTP01_DateTimeQualifier>
                      <DTP02_DateTimePeriodFormatQualifier>RD8</DTP02_DateTimePeriodFormatQualifier>
                      <DTP03_ServiceDate>20160627-20160627</DTP03_ServiceDate>
                    </ns0:DTP_Date_ServiceDate>
                  </ns0:DTP_SubLoop_2>
                </ns0:TS837_2400_Loop>
              </ns0:TS837_2300_Loop>
            </ns0:TS837_2000B_Loop>
          </ns0:TS837_2000A_Loop>
        </ns0:X12_00501_837_P>
      </TransactionSet>
    </ns1:X12EnrichedMessage>'

    IF OBJECT_ID(N'tempdb..#CLAIM_XML', N'U') IS NOT NULL
      DROP TABLE #CLAIM_XML;

    CREATE TABLE #CLAIM_XML (
      CLAIM_SOURCE_ID VARCHAR(20) NOT NULL
     ,RAW_XML XML NOT NULL
     ,CLAIM_FORM_TYPE CHAR(1) NOT NULL
     ,CREATED_DATE DATE NOT NULL
     ,CONSTRAINT CLAIM_XML_PK PRIMARY KEY (CLAIM_SOURCE_ID)
    );

    CREATE PRIMARY XML INDEX CLAIM_XML_RAW_XML_IDX
      ON #CLAIM_XML (RAW_XML);

    INSERT INTO #CLAIM_XML
          ([CLAIM_SOURCE_ID]
          ,[RAW_XML]
          ,[CLAIM_FORM_TYPE]
          ,[CREATED_DATE])
    VALUES('16202E123456'
          ,@XML
          ,'H'
          ,CONVERT(DATE, DATEADD(DAY, -1, GETDATE())));

    WITH XMLNAMESPACES ('http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML' AS ns1
                       ,'http://schemas.microsoft.com/BizTalk/EDI/X12/2006' AS ns0)
    SELECT [CX].[CLAIM_SOURCE_ID]
          ,[ITEM_NUMBER] = LineNumber.ref.value('text()[1]', 'int')
          ,[HCPCS_LINE_CODE] = [CX].[RAW_XML].value('(/ns1:X12EnrichedMessage/TransactionSet/ns0:X12_00501_837_P/ns0:TS837_2000A_Loop/ns0:TS837_2000B_Loop/ns0:TS837_2300_Loop/ns0:TS837_2400_Loop/ns0:SV1_ProfessionalService/ns0:C003_CompositeMedicalProcedureIdentifier/C00302_ProcedureCode)[1]','varchar(100)')
      FROM #CLAIM_XML AS [CX]
     CROSS APPLY [CX].[RAW_XML].nodes('/ns1:X12EnrichedMessage/TransactionSet/ns0:X12_00501_837_P/ns0:TS837_2000A_Loop/ns0:TS837_2000B_Loop/ns0:TS837_2300_Loop/ns0:TS837_2400_Loop/ns0:LX_ServiceLineNumber/*') LineNumber(ref)
     WHERE [CX].[CLAIM_FORM_TYPE] = 'H'
       AND [CX].[CREATED_DATE] = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()));

wBob · Accepted Answer · 2016-09-29 20:23:04Z

1

Use multiple CROSS APPLYs to access the different parts of the XML, like this:

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML' AS ns1
                    ,'http://schemas.microsoft.com/BizTalk/EDI/X12/2006' AS ns0)
SELECT 
    c.[CLAIM_SOURCE_ID],
    sln.c.value('(LX01_AssignedNumber/text())[1]', 'INT') AS [ITEM_NUMBER],
    ps.c.value('(ns0:C003_CompositeMedicalProcedureIdentifier/C00302_ProcedureCode/text())[1]', 'INT') AS [HCPCS_LINE_CODE]
FROM #CLAIM_XML c
    CROSS APPLY c.[RAW_XML].nodes('/ns1:X12EnrichedMessage/TransactionSet/ns0:X12_00501_837_P/ns0:TS837_2000A_Loop/ns0:TS837_2000B_Loop/ns0:TS837_2300_Loop/ns0:TS837_2400_Loop') l(c)
        CROSS APPLY l.c.nodes('ns0:LX_ServiceLineNumber') sln(c)
        CROSS APPLY l.c.nodes('ns0:SV1_ProfessionalService') ps(c)

answered Sep 29, 2016 at 20:23

wBob

14.4k3 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

Hi wBob, the deeper CROSS APPLY are not needed... You would need them, if you'd expect several occurances of ns0:LX_ServiceLineNumber or ns0:SV1_ProfessionalService. It should be shlightly faster to address these values directly.

Gottfried Lesigang · Accepted Answer · 2016-09-30 09:14:53Z

No need for multiple CROSS APPLY with .nodes(). As the values you want to read are single occurance within their tree you can address them directly:

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML' AS ns1
                    ,'http://schemas.microsoft.com/BizTalk/EDI/X12/2006' AS ns0)
SELECT 
    loop2400.value('(ns0:LX_ServiceLineNumber/LX01_AssignedNumber)[1]', 'INT') AS [ITEM_NUMBER],
    loop2400.value('(ns0:SV1_ProfessionalService/ns0:C003_CompositeMedicalProcedureIdentifier/C00302_ProcedureCode)[1]', 'INT') AS [HCPCS_LINE_CODE]
FROM @xml.nodes('/ns1:X12EnrichedMessage/TransactionSet/ns0:X12_00501_837_P/ns0:TS837_2000A_Loop/ns0:TS837_2000B_Loop/ns0:TS837_2300_Loop/ns0:TS837_2400_Loop') A(loop2400)

The lazy approach works too, but - in general - it's good advise to be as specific as possible...

SELECT 
    loop2400.value('(*//LX01_AssignedNumber)[1]', 'INT') AS [ITEM_NUMBER],
    loop2400.value('(*//C00302_ProcedureCode)[1]', 'INT') AS [HCPCS_LINE_CODE]
FROM @xml.nodes('//*:TS837_2400_Loop') A(loop2400)

Collectives™ on Stack Overflow

sql server query xml related nodes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related