Multiple values from XML column

Question

I am trying to figure out how to get multiple values from multiple nodes of an XML field in a table (actually it's XML stored as text).

I've seen several methods that involve declaring the XML as a variable and using it as a table but I don't see how that would work for me. How to Extract data from xml column in sql 2008

I am currently using .value to get some fields but I don't see how to make it work since there can be multiple LX01_AssignedNumber and I need to get all of the ProcedureModifier from each.

SELECT  CAST(xmldata as xml).value('declare namespace ns1="http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML";declare namespace ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006";
(/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/C00303_ProcedureModifier) [1]', 'varchar(20)') AS RendAttendNPI 
FROM EDI_DATA

How do I get all the Line Numbers and all of the Procedure Modifiers from each record?

XML:

<ns1:X12EnrichedMessage xmlns:ns1="http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML">
...
    <TransactionSet>
        <!-- ProcessLogID=PLG0005169955  ;ProcessLogDetailID=PLG0005173285  ;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>26340</C00302_ProcedureCode>
                                    <C00303_ProcedureModifier>AG</C00303_ProcedureModifier>
                                    <C00304_ProcedureModifier>58</C00304_ProcedureModifier>
                                    <C00305_ProcedureModifier>51</C00305_ProcedureModifier>
                                    <C00306_ProcedureModifier>XS</C00306_ProcedureModifier>
                                </ns0:C003_CompositeMedicalProcedureIdentifier>
                                <SV102_LineItemChargeAmount>8918</SV102_LineItemChargeAmount>
                                <SV103_UnitorBasisforMeasurementCode>UN</SV103_UnitorBasisforMeasurementCode>
                                <SV104_ServiceUnitCount>13</SV104_ServiceUnitCount>
                                <ns0:C004_CompositeDiagnosisCodePointer>
                                    <C00401_DiagnosisCodePointer>1</C00401_DiagnosisCodePointer>
                                    <C00402_DiagnosisCodePointer>2</C00402_DiagnosisCodePointer>
                                </ns0:C004_CompositeDiagnosisCodePointer>
                            </ns0:SV1_ProfessionalService>
                            <ns0:DTP_SubLoop_2>
                                <ns0:DTP_Date_ServiceDate>
                                    <DTP01_DateTimeQualifier>472</DTP01_DateTimeQualifier>
                                    <DTP02_DateTimePeriodFormatQualifier>D8</DTP02_DateTimePeriodFormatQualifier>
                                    <DTP03_ServiceDate>20160104</DTP03_ServiceDate>
                                </ns0:DTP_Date_ServiceDate>
                            </ns0:DTP_SubLoop_2>
                            <ns0:REF_SubLoop_7>
                                <ns0:REF_LineItemControlNumber>
                                    <REF01_ReferenceIdentificationQualifier>6R</REF01_ReferenceIdentificationQualifier>
                                    <REF02_LineItemControlNumber>11453481</REF02_LineItemControlNumber>
                                </ns0:REF_LineItemControlNumber>
                            </ns0:REF_SubLoop_7>
                        </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>20680</C00302_ProcedureCode>
                                    <C00303_ProcedureModifier>58</C00303_ProcedureModifier>
                                </ns0:C003_CompositeMedicalProcedureIdentifier>
                                <SV102_LineItemChargeAmount>1277</SV102_LineItemChargeAmount>
                                <SV103_UnitorBasisforMeasurementCode>UN</SV103_UnitorBasisforMeasurementCode>
                                <SV104_ServiceUnitCount>1</SV104_ServiceUnitCount>
                                <ns0:C004_CompositeDiagnosisCodePointer>
                                    <C00401_DiagnosisCodePointer>3</C00401_DiagnosisCodePointer>
                                </ns0:C004_CompositeDiagnosisCodePointer>
                            </ns0:SV1_ProfessionalService>
                        </ns0:TS837_2400_Loop>
                    </ns0:TS837_2300_Loop>
                </ns0:TS837_2000B_Loop>
            </ns0:TS837_2000A_Loop>
        </ns0:X12_00501_837_P>
    </TransactionSet>
</ns1:X12EnrichedMessage>

score 1 · Accepted Answer · answered Jan 26 '16 at 03:18

1

Look into SQL Server CROSS APPLY which you can use to shred single XML data into multiple rows, for example :

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/BizTalk/EDI/X12/2006' as ns0
                     ,'http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006/EnrichedMessageXML' as ns1)

SELECT 
    TS837_2400_Loop.value('(.//LX01_AssignedNumber)[1]', 'int') 'line_number'
    ,C00303_ProcedureModifier.value('.', 'varchar(100)') 'procedure_modifier'
FROM EDI_DATA
    CROSS APPLY (select CONVERT(XML, xmldata)) as P(X)
    CROSS APPLY X.nodes('.//ns0:TS837_2400_Loop') AS Q(TS837_2400_Loop)
    CROSS APPLY TS837_2400_Loop.nodes('.//C00303_ProcedureModifier') AS R(C00303_ProcedureModifier)

sqlfiddle demo

output :

| line_number | procedure_modifier |
|-------------|--------------------|
|           1 |                 AG |
|           2 |                 58 |

answered Jan 26 '16 at 03:18

har07

88,338
12
84
137

Ah - that looks more like what I want than the other XML parsing I have found. Thanks. I'll try to get this working in the next couple of days. I got stuck in jury duty the past 2 days and am behind on everything now. – Hannover Fist Jan 28 '16 at 17:14
Hey har07 - is there a way to make the procedure codes (C0030**x**_ProcedureModifier) dynamic? There can be five of them. Or is the only way to hard code them all and they'll be NULL if they don't exist? – Hannover Fist Jan 30 '16 at 00:19
Thanks - this is working well now. I wish I could make procedure codes dynamic but that may not be possible due to each code having a different name. – Hannover Fist Feb 01 '16 at 21:27

Multiple values from XML column

1 Answers1