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>
No comments:
Post a Comment