locked
Stored Proc - with XML passed as parameter RRS feed

  • Question

  • Hi - below is my Database diagram. How can i write a stored proc for this, with XML passing to this as a parameter. 

    Please let me know if any further details needs to be know


    MBH

    Monday, September 26, 2016 7:25 PM

Answers

All replies

  • 42?

    Your question is rather broad.  What do you want to do?  What is the problem you are trying to solve?  Why are you considering XML?


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, September 26, 2016 7:37 PM
  • I will be receiving XML as a parameter from external source, I have to load (insert) the data in the above tables.

    I want to write stored proc for this, so that insert is done in all these table simultaneously. 

    All this be in 1 stored proc


    MBH

    Monday, September 26, 2016 7:53 PM
  • I assume that you have some data in all of these tables that relate to each other.  I would start by building a FOR XML path query to create an example XML output.  You will probably need a couple correlated subqueries to produce child table results.  I would take that output and edit it into a format where all of the information is clear as to where you want it to go.

    You need to make sure that you have some natural keys that you can use when you load your child tables. 

    You will need a set of queries using the xml XQuery nodes method to shred the XML.  If you load a set of rows that have related table data, then you will need to OUTPUT the results of your INSERT (mainly the natural key and the surrogate key).  OUTPUT to a temp table.  You will use the rows in this temp table to fill the child tables.

    If you are comfortable using FOR XML and the xml data type, this is perhaps a day's work or more.  If you don't, then I would suggest learning as much as you can about these.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, September 26, 2016 8:05 PM
  • I already have a structure of XML that I will be passing to SQL for Insert. The XML is as below which has all elements mentioned in the tables coloumns. I need to write stored proc for this : How can I ?

    <?xml version="1.0" encoding="utf-8"?><ns0:ORU_R01_231_GLO_DEF xmlns:ns0="http://MHP.Biztalk.HL7"><PID_PatientIdentificationSegment><PID_1_SetIdPid>57</PID_1_SetIdPid><PID_2_PatientId><CX_0_Id>EP10002</CX_0_Id></PID_2_PatientId><PID_3_PatientIdentifierList><CX_0_Id>AN10002</CX_0_Id></PID_3_PatientIdentifierList><PID_4_AlternatePatientIdPid><CX_0_Id>RN10002</CX_0_Id></PID_4_AlternatePatientIdPid><PID_5_PatientName><XPN_0_FamilyLastName><XPN_0_0_FamilyName>LastNM10002</XPN_0_0_FamilyName></XPN_0_FamilyLastName><XPN_1_GivenName>FirstNM10002</XPN_1_GivenName><XPN_2_MiddleInitialOrName>MI10002</XPN_2_MiddleInitialOrName></PID_5_PatientName><PID_6_MotherSMaidenName><XPN_0_FamilyLastName><XPN_0_0_FamilyName></XPN_0_0_FamilyName></XPN_0_FamilyLastName></PID_6_MotherSMaidenName><PID_7_DateTimeOfBirth><TS_0_TimeOfAnEvent>20130504</TS_0_TimeOfAnEvent></PID_7_DateTimeOfBirth><PID_8_Sex>F</PID_8_Sex><PID_9_PatientAlias><XPN_0_FamilyLastName><XPN_0_0_FamilyName></XPN_0_0_FamilyName></XPN_0_FamilyLastName></PID_9_PatientAlias><PID_10_Race><CE_0005_0_Identifier></CE_0005_0_Identifier></PID_10_Race><PID_11_PatientAddress><XAD_0_StreetAddress>Street10002</XAD_0_StreetAddress><XAD_1_OtherDesignation></XAD_1_OtherDesignation><XAD_2_City>City10002</XAD_2_City><XAD_3_StateOrProvince>XX</XAD_3_StateOrProvince><XAD_4_ZipOrPostalCode>10002</XAD_4_ZipOrPostalCode></PID_11_PatientAddress><PID_12_CountyCode></PID_12_CountyCode><PID_13_PhoneNumberHome><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText><XTN_1_TelecommunicationUseCode></XTN_1_TelecommunicationUseCode><XTN_2_TelecommunicationEquipmentTypeId></XTN_2_TelecommunicationEquipmentTypeId><XTN_3_EmailAddress></XTN_3_EmailAddress><XTN_4_CountryCode></XTN_4_CountryCode><XTN_5_AreaCityCode>999</XTN_5_AreaCityCode><XTN_6_PhoneNumber>9991002</XTN_6_PhoneNumber></PID_13_PhoneNumberHome><PID_14_PhoneNumberBusiness><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText></PID_14_PhoneNumberBusiness><PID_15_PrimaryLanguage><CE_0296_0_Identifier></CE_0296_0_Identifier></PID_15_PrimaryLanguage><PID_16_MaritalStatus><CE_0002_0_Identifier></CE_0002_0_Identifier></PID_16_MaritalStatus><PID_17_Religion><CE_0006_0_Identifier></CE_0006_0_Identifier></PID_17_Religion><PID_18_PatientAccountNumber><CX_0_Id></CX_0_Id></PID_18_PatientAccountNumber><PID_19_SsnNumberPatient>9999991002</PID_19_SsnNumberPatient></PID_PatientIdentificationSegment><IN1><IN1_1_SetIdIn1>1</IN1_1_SetIdIn1><IN1_2_InsurancePlanId><CE_0072_0_Identifier>AAO</CE_0072_0_Identifier></IN1_2_InsurancePlanId><IN1_3_InsuranceCompanyId><CX_0_Id></CX_0_Id></IN1_3_InsuranceCompanyId><IN1_4_InsuranceCompanyName><XON_0_OrganizationName></XON_0_OrganizationName></IN1_4_InsuranceCompanyName><IN1_5_InsuranceCompanyAddress><XAD_0_StreetAddress></XAD_0_StreetAddress></IN1_5_InsuranceCompanyAddress><IN1_6_InsuranceCoContactPerson><XPN_0_FamilyLastName><XPN_0_0_FamilyName></XPN_0_0_FamilyName></XPN_0_FamilyLastName></IN1_6_InsuranceCoContactPerson><IN1_7_InsuranceCoPhoneNumber><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText></IN1_7_InsuranceCoPhoneNumber><IN1_8_GroupNumber>895530</IN1_8_GroupNumber><IN1_9_GroupName><XON_0_OrganizationName></XON_0_OrganizationName></IN1_9_GroupName><IN1_10_InsuredSGroupEmpId><CX_0_Id></CX_0_Id></IN1_10_InsuredSGroupEmpId><IN1_11_InsuredSGroupEmpName><XON_0_OrganizationName></XON_0_OrganizationName></IN1_11_InsuredSGroupEmpName><IN1_12_PlanEffectiveDate></IN1_12_PlanEffectiveDate><IN1_13_PlanExpirationDate></IN1_13_PlanExpirationDate><IN1_14_AuthorizationInformation><AUI_0_AuthorizationNumber></AUI_0_AuthorizationNumber></IN1_14_AuthorizationInformation><IN1_15_PlanType></IN1_15_PlanType><IN1_16_NameOfInsured><XPN_0_FamilyLastName><XPN_0_0_FamilyName></XPN_0_0_FamilyName></XPN_0_FamilyLastName></IN1_16_NameOfInsured><IN1_17_InsuredSRelationshipToPatient><CE_0063_0_Identifier></CE_0063_0_Identifier></IN1_17_InsuredSRelationshipToPatient><IN1_18_InsuredSDateOfBirth><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></IN1_18_InsuredSDateOfBirth><IN1_19_InsuredSAddress><XAD_0_StreetAddress></XAD_0_StreetAddress></IN1_19_InsuredSAddress><IN1_20_AssignmentOfBenefits></IN1_20_AssignmentOfBenefits><IN1_21_CoordinationOfBenefits></IN1_21_CoordinationOfBenefits><IN1_22_CoordOfBenPriority></IN1_22_CoordOfBenPriority><IN1_23_NoticeOfAdmissionFlag></IN1_23_NoticeOfAdmissionFlag><IN1_24_NoticeOfAdmissionDate></IN1_24_NoticeOfAdmissionDate><IN1_25_ReportOfEligibilityFlag></IN1_25_ReportOfEligibilityFlag><IN1_26_ReportOfEligibilityDate></IN1_26_ReportOfEligibilityDate><IN1_27_ReleaseInformationCode></IN1_27_ReleaseInformationCode><IN1_28_PreAdmitCertPac></IN1_28_PreAdmitCertPac><IN1_29_VerificationDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></IN1_29_VerificationDateTime><IN1_30_VerificationBy><XCN_0_IdNumberSt></XCN_0_IdNumberSt></IN1_30_VerificationBy><IN1_31_TypeOfAgreementCode></IN1_31_TypeOfAgreementCode><IN1_32_BillingStatus></IN1_32_BillingStatus><IN1_33_LifetimeReserveDays></IN1_33_LifetimeReserveDays><IN1_34_DelayBeforeLRDay></IN1_34_DelayBeforeLRDay><IN1_35_CompanyPlanCode></IN1_35_CompanyPlanCode><IN1_36_PolicyNumber>PN10002</IN1_36_PolicyNumber></IN1><ORC_CommonOrderSegment><ORC_1_OrderControl></ORC_1_OrderControl><ORC_2_PlacerOrderNumber><EI_0_EntityIdentifier></EI_0_EntityIdentifier></ORC_2_PlacerOrderNumber><ORC_3_FillerOrderNumber><EI_0_EntityIdentifier>AN10002</EI_0_EntityIdentifier></ORC_3_FillerOrderNumber><ORC_4_PlacerGroupNumber><EI_0_EntityIdentifier></EI_0_EntityIdentifier></ORC_4_PlacerGroupNumber><ORC_5_OrderStatus></ORC_5_OrderStatus><ORC_6_ResponseFlag></ORC_6_ResponseFlag><ORC_7_QuantityTiming><TQ_0_Quantity><TQ_0_0_Quantity></TQ_0_0_Quantity></TQ_0_Quantity></ORC_7_QuantityTiming><ORC_8_Parent><EIP_0_ParentSPlacerOrderNumber><EIP_0_0_EntityIdentifier></EIP_0_0_EntityIdentifier></EIP_0_ParentSPlacerOrderNumber></ORC_8_Parent><ORC_9_DateTimeOfTransaction><TS_0_TimeOfAnEvent>20131107</TS_0_TimeOfAnEvent></ORC_9_DateTimeOfTransaction><ORC_10_EnteredBy><XCN_0_IdNumberSt></XCN_0_IdNumberSt></ORC_10_EnteredBy><ORC_11_VerifiedBy><XCN_0_IdNumberSt></XCN_0_IdNumberSt></ORC_11_VerifiedBy><ORC_12_OrderingProvider><XCN_0_IdNumberSt>UPIN10002</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM10002</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM10002</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI10002</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>UPIN</XCN_7_SourceTable></ORC_12_OrderingProvider><ORC_12_OrderingProvider><XCN_0_IdNumberSt>NPI10002</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM10002</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM10002</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI10002</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>NPI</XCN_7_SourceTable></ORC_12_OrderingProvider><ORC_13_EntererSLocation><PL_0_PointOfCare></PL_0_PointOfCare></ORC_13_EntererSLocation><ORC_14_CallBackPhoneNumber><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText></ORC_14_CallBackPhoneNumber><ORC_15_OrderEffectiveDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></ORC_15_OrderEffectiveDateTime><ORC_16_OrderControlCodeReason><CE_0_Identifier></CE_0_Identifier></ORC_16_OrderControlCodeReason><ORC_17_EnteringOrganization><CE_0_Identifier></CE_0_Identifier></ORC_17_EnteringOrganization><ORC_18_EnteringDevice><CE_0_Identifier></CE_0_Identifier></ORC_18_EnteringDevice><ORC_19_ActionBy><XCN_0_IdNumberSt></XCN_0_IdNumberSt></ORC_19_ActionBy><ORC_20_AdvancedBeneficiaryNoticeCode><CE_0339_0_Identifier></CE_0339_0_Identifier></ORC_20_AdvancedBeneficiaryNoticeCode><ORC_21_OrderingFacilityName><XON_0_OrganizationName>OrderFacilityName10002</XON_0_OrganizationName><XON_1_OrganizationNameTypeCode>D</XON_1_OrganizationNameTypeCode><XON_2_IdNumberNm>10002</XON_2_IdNumberNm></ORC_21_OrderingFacilityName><ORC_22_OrderingFacilityAddress><XAD_0_StreetAddress>Street10002</XAD_0_StreetAddress><XAD_1_OtherDesignation></XAD_1_OtherDesignation><XAD_2_City>City10002</XAD_2_City><XAD_3_StateOrProvince>XX</XAD_3_StateOrProvince><XAD_4_ZipOrPostalCode>10002</XAD_4_ZipOrPostalCode></ORC_22_OrderingFacilityAddress><ORC_23_OrderingFacilityPhoneNumber><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText><XTN_1_TelecommunicationUseCode></XTN_1_TelecommunicationUseCode><XTN_2_TelecommunicationEquipmentTypeId></XTN_2_TelecommunicationEquipmentTypeId><XTN_3_EmailAddress></XTN_3_EmailAddress><XTN_4_CountryCode></XTN_4_CountryCode><XTN_5_AreaCityCode>999</XTN_5_AreaCityCode><XTN_6_PhoneNumber>9991002</XTN_6_PhoneNumber></ORC_23_OrderingFacilityPhoneNumber><ORC_24_OrderingProviderAddress><XAD_0_StreetAddress></XAD_0_StreetAddress></ORC_24_OrderingProviderAddress></ORC_CommonOrderSegment><DG1><DG1_1_SetIdDg1>1</DG1_1_SetIdDg1><DG1_2_DiagnosisCodingMethod>I09</DG1_2_DiagnosisCodingMethod><DG1_3>7935</DG1_3></DG1><OBR_ObservationRequestSegment><OBR_1_SetIdObr>1</OBR_1_SetIdObr><OBR_2_PlacerOrderNumber><EI_0_EntityIdentifier></EI_0_EntityIdentifier></OBR_2_PlacerOrderNumber><OBR_3_FillerOrderNumber><EI_0_EntityIdentifier>AN100058</EI_0_EntityIdentifier></OBR_3_FillerOrderNumber><OBR_4_UniversalServiceId><CE_0_Identifier>6600003542</CE_0_Identifier><CE_1_Text>TISSUE PATHOLOGY</CE_1_Text><CE_2_NameOfCodingSystem>L</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>{NB}</CE_3_AlternateIdentifier><CE_4_AlternateText></CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBR_4_UniversalServiceId><OBR_5_PriorityObr></OBR_5_PriorityObr><OBR_6_RequestedDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_6_RequestedDateTime><OBR_7_ObservationDateTime><TS_0_TimeOfAnEvent>20131104</TS_0_TimeOfAnEvent></OBR_7_ObservationDateTime><OBR_8_ObservationEndDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_8_ObservationEndDateTime><OBR_9_CollectionVolume><CQ_0_Quantity></CQ_0_Quantity></OBR_9_CollectionVolume><OBR_10_CollectorIdentifier><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBR_10_CollectorIdentifier><OBR_11_SpecimenActionCode></OBR_11_SpecimenActionCode><OBR_12_DangerCode><CE_0_Identifier></CE_0_Identifier></OBR_12_DangerCode><OBR_13_RelevantClinicalInfo></OBR_13_RelevantClinicalInfo><OBR_14_SpecimenReceivedDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_14_SpecimenReceivedDateTime><OBR_15_SpecimenSource><SPS_0_SpecimenSourceNameOrCode><SPS_0_0_Identifier></SPS_0_0_Identifier></SPS_0_SpecimenSourceNameOrCode></OBR_15_SpecimenSource><OBR_16_OrderingProvider><XCN_0_IdNumberSt>UPIN100058</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM100058</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM100058</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI100058</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>UPIN</XCN_7_SourceTable></OBR_16_OrderingProvider><OBR_16_OrderingProvider><XCN_0_IdNumberSt>NPI100058</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM100058</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM100058</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI100058</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>NPI</XCN_7_SourceTable></OBR_16_OrderingProvider><OBR_17_OrderCallbackPhoneNumber><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText></OBR_17_OrderCallbackPhoneNumber><OBR_18_PlacerField1></OBR_18_PlacerField1><OBR_19_PlacerField2></OBR_19_PlacerField2><OBR_20_FillerField1>WDL</OBR_20_FillerField1><OBR_21_FillerField2></OBR_21_FillerField2><OBR_22_ResultsRptStatusChngDateTime><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBR_22_ResultsRptStatusChngDateTime><OBR_23_ChargeToPractice><MOC_0_DollarAmount><MOC_0_0_Quantity></MOC_0_0_Quantity></MOC_0_DollarAmount></OBR_23_ChargeToPractice><OBR_24_DiagnosticServSectId></OBR_24_DiagnosticServSectId><OBR_25_ResultStatus></OBR_25_ResultStatus><OBR_26_ParentResult><PRL_0_Obx3ObservationIdentifierOfParentResult><PRL_0_0_Identifier></PRL_0_0_Identifier></PRL_0_Obx3ObservationIdentifierOfParentResult></OBR_26_ParentResult><OBR_27_QuantityTiming><TQ_0_Quantity><TQ_0_0_Quantity></TQ_0_0_Quantity></TQ_0_Quantity></OBR_27_QuantityTiming><OBR_28_ResultCopiesTo><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBR_28_ResultCopiesTo><OBR_29_Parent><EIP_0_ParentSPlacerOrderNumber><EIP_0_0_EntityIdentifier></EIP_0_0_EntityIdentifier></EIP_0_ParentSPlacerOrderNumber></OBR_29_Parent><OBR_30_TransportationMode></OBR_30_TransportationMode><OBR_31_ReasonForStudy><CE_0_Identifier></CE_0_Identifier></OBR_31_ReasonForStudy><OBR_32_PrincipalResultInterpreter><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_32_PrincipalResultInterpreter><OBR_33_AssistantResultInterpreter><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_33_AssistantResultInterpreter><OBR_34_Technician><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_34_Technician><OBR_35_Transcriptionist><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_35_Transcriptionist><OBR_36_ScheduledDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_36_ScheduledDateTime><OBR_37_NumberOfSampleContainers></OBR_37_NumberOfSampleContainers><OBR_38_TransportLogisticsOfCollectedSample><CE_0_Identifier></CE_0_Identifier></OBR_38_TransportLogisticsOfCollectedSample><OBR_39_CollectorSComment><CE_0_Identifier></CE_0_Identifier></OBR_39_CollectorSComment><OBR_40_TransportArrangementResponsibility><CE_0_Identifier></CE_0_Identifier></OBR_40_TransportArrangementResponsibility><OBR_41_TransportArranged></OBR_41_TransportArranged><OBR_42_EscortRequired></OBR_42_EscortRequired><OBR_43_PlannedPatientTransportComment><CE_0_Identifier></CE_0_Identifier></OBR_43_PlannedPatientTransportComment><OBR_44_ProcedureCode><CE_0088_0_Identifier></CE_0088_0_Identifier><CE_0088_1_Text></CE_0088_1_Text><CE_0088_2_NameOfCodingSystem>CPT</CE_0088_2_NameOfCodingSystem></OBR_44_ProcedureCode><OBR_45_ProcedureCodeModifier><CE_0340_0_Identifier></CE_0340_0_Identifier></OBR_45_ProcedureCodeModifier></OBR_ObservationRequestSegment><OBX_ObservationResultSegment><OBX_1_SetIdObx>1</OBX_1_SetIdObx><OBX_2_ValueType>ST</OBX_2_ValueType><OBX_3_ObservationIdentifier><CE_0_Identifier>19139-5</CE_0_Identifier><CE_1_Text></CE_1_Text><CE_2_NameOfCodingSystem>LOINC</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>90020991</CE_3_AlternateIdentifier><CE_4_AlternateText>PATHOLOGIST</CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBX_3_ObservationIdentifier><OBX_4_ObservationSubId></OBX_4_ObservationSubId><OBX_5_ObservationValue>SEE NOTE</OBX_5_ObservationValue><OBX_6_Units><CE_0_Identifier></CE_0_Identifier></OBX_6_Units><OBX_7><RangeLow></RangeLow><RangeHigh></RangeHigh><RangeAlpha></RangeAlpha></OBX_7><OBX_8_AbnormalFlags></OBX_8_AbnormalFlags><OBX_9_Probability></OBX_9_Probability><OBX_10_NatureOfAbnormalTest></OBX_10_NatureOfAbnormalTest><OBX_11_ObservationResultStatus>F</OBX_11_ObservationResultStatus><OBX_12_DateLastObsNormalValues><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBX_12_DateLastObsNormalValues><OBX_13_UserDefinedAccessChecks></OBX_13_UserDefinedAccessChecks><OBX_14_DateTimeOfTheObservation><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBX_14_DateTimeOfTheObservation><OBX_15_ProducerSId><CE_0_Identifier></CE_0_Identifier></OBX_15_ProducerSId><OBX_16_ResponsibleObserver><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBX_16_ResponsibleObserver><OBX_17_ObservationMethod><CE_0_Identifier></CE_0_Identifier></OBX_17_ObservationMethod></OBX_ObservationResultSegment><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>1</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Shaheen A Imam, M.D., Board Certified in Anatomic</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>2</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Pathology, Clinical Pathology and Hematopathology</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>3</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>1 800 669 6995 ext. 5678 (electronic signature)</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><OBR_ObservationRequestSegment><OBR_1_SetIdObr>2</OBR_1_SetIdObr><OBR_2_PlacerOrderNumber><EI_0_EntityIdentifier></EI_0_EntityIdentifier></OBR_2_PlacerOrderNumber><OBR_3_FillerOrderNumber><EI_0_EntityIdentifier>AN100058</EI_0_EntityIdentifier></OBR_3_FillerOrderNumber><OBR_4_UniversalServiceId><CE_0_Identifier>6600010799</CE_0_Identifier><CE_1_Text>TISSUE, SPECIMEN A</CE_1_Text><CE_2_NameOfCodingSystem>L</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>{NB}</CE_3_AlternateIdentifier><CE_4_AlternateText></CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBR_4_UniversalServiceId><OBR_5_PriorityObr></OBR_5_PriorityObr><OBR_6_RequestedDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_6_RequestedDateTime><OBR_7_ObservationDateTime><TS_0_TimeOfAnEvent>20131104</TS_0_TimeOfAnEvent></OBR_7_ObservationDateTime><OBR_8_ObservationEndDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_8_ObservationEndDateTime><OBR_9_CollectionVolume><CQ_0_Quantity></CQ_0_Quantity></OBR_9_CollectionVolume><OBR_10_CollectorIdentifier><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBR_10_CollectorIdentifier><OBR_11_SpecimenActionCode></OBR_11_SpecimenActionCode><OBR_12_DangerCode><CE_0_Identifier></CE_0_Identifier></OBR_12_DangerCode><OBR_13_RelevantClinicalInfo></OBR_13_RelevantClinicalInfo><OBR_14_SpecimenReceivedDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_14_SpecimenReceivedDateTime><OBR_15_SpecimenSource><SPS_0_SpecimenSourceNameOrCode><SPS_0_0_Identifier></SPS_0_0_Identifier></SPS_0_SpecimenSourceNameOrCode></OBR_15_SpecimenSource><OBR_16_OrderingProvider><XCN_0_IdNumberSt>UPIN100058</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM100058</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM100058</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI100058</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>UPIN</XCN_7_SourceTable></OBR_16_OrderingProvider><OBR_16_OrderingProvider><XCN_0_IdNumberSt>NPI100058</XCN_0_IdNumberSt><XCN_1_FamilyLastName><XCN_1_0_FamilyName>LastNM100058</XCN_1_0_FamilyName></XCN_1_FamilyLastName><XCN_2_GivenName>FirstNM100058</XCN_2_GivenName><XCN_3_MiddleInitialOrName>MI100058</XCN_3_MiddleInitialOrName><XCN_4_SuffixEGJrOrIii></XCN_4_SuffixEGJrOrIii><XCN_5_PrefixEGDr></XCN_5_PrefixEGDr><XCN_6_DegreeEGMd></XCN_6_DegreeEGMd><XCN_7_SourceTable>NPI</XCN_7_SourceTable></OBR_16_OrderingProvider><OBR_17_OrderCallbackPhoneNumber><XTN_0_9999999999X99999CAnyText></XTN_0_9999999999X99999CAnyText></OBR_17_OrderCallbackPhoneNumber><OBR_18_PlacerField1></OBR_18_PlacerField1><OBR_19_PlacerField2></OBR_19_PlacerField2><OBR_20_FillerField1>WDL</OBR_20_FillerField1><OBR_21_FillerField2></OBR_21_FillerField2><OBR_22_ResultsRptStatusChngDateTime><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBR_22_ResultsRptStatusChngDateTime><OBR_23_ChargeToPractice><MOC_0_DollarAmount><MOC_0_0_Quantity></MOC_0_0_Quantity></MOC_0_DollarAmount></OBR_23_ChargeToPractice><OBR_24_DiagnosticServSectId></OBR_24_DiagnosticServSectId><OBR_25_ResultStatus></OBR_25_ResultStatus><OBR_26_ParentResult><PRL_0_Obx3ObservationIdentifierOfParentResult><PRL_0_0_Identifier></PRL_0_0_Identifier></PRL_0_Obx3ObservationIdentifierOfParentResult></OBR_26_ParentResult><OBR_27_QuantityTiming><TQ_0_Quantity><TQ_0_0_Quantity></TQ_0_0_Quantity></TQ_0_Quantity></OBR_27_QuantityTiming><OBR_28_ResultCopiesTo><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBR_28_ResultCopiesTo><OBR_29_Parent><EIP_0_ParentSPlacerOrderNumber><EIP_0_0_EntityIdentifier></EIP_0_0_EntityIdentifier></EIP_0_ParentSPlacerOrderNumber></OBR_29_Parent><OBR_30_TransportationMode></OBR_30_TransportationMode><OBR_31_ReasonForStudy><CE_0_Identifier></CE_0_Identifier></OBR_31_ReasonForStudy><OBR_32_PrincipalResultInterpreter><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_32_PrincipalResultInterpreter><OBR_33_AssistantResultInterpreter><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_33_AssistantResultInterpreter><OBR_34_Technician><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_34_Technician><OBR_35_Transcriptionist><NDL_0_Name><NDL_0_0_IdNumberSt></NDL_0_0_IdNumberSt></NDL_0_Name></OBR_35_Transcriptionist><OBR_36_ScheduledDateTime><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBR_36_ScheduledDateTime><OBR_37_NumberOfSampleContainers></OBR_37_NumberOfSampleContainers><OBR_38_TransportLogisticsOfCollectedSample><CE_0_Identifier></CE_0_Identifier></OBR_38_TransportLogisticsOfCollectedSample><OBR_39_CollectorSComment><CE_0_Identifier></CE_0_Identifier></OBR_39_CollectorSComment><OBR_40_TransportArrangementResponsibility><CE_0_Identifier></CE_0_Identifier></OBR_40_TransportArrangementResponsibility><OBR_41_TransportArranged></OBR_41_TransportArranged><OBR_42_EscortRequired></OBR_42_EscortRequired><OBR_43_PlannedPatientTransportComment><CE_0_Identifier></CE_0_Identifier></OBR_43_PlannedPatientTransportComment><OBR_44_ProcedureCode><CE_0088_0_Identifier></CE_0088_0_Identifier><CE_0088_1_Text></CE_0088_1_Text><CE_0088_2_NameOfCodingSystem>CPT</CE_0088_2_NameOfCodingSystem></OBR_44_ProcedureCode><OBR_45_ProcedureCodeModifier><CE_0340_0_Identifier></CE_0340_0_Identifier></OBR_45_ProcedureCodeModifier></OBR_ObservationRequestSegment><OBX_ObservationResultSegment><OBX_1_SetIdObx>1</OBX_1_SetIdObx><OBX_2_ValueType>ST</OBX_2_ValueType><OBX_3_ObservationIdentifier><CE_0_Identifier>31208-2</CE_0_Identifier><CE_1_Text></CE_1_Text><CE_2_NameOfCodingSystem>LOINC</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>90000035</CE_3_AlternateIdentifier><CE_4_AlternateText>A SOURCE</CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBX_3_ObservationIdentifier><OBX_4_ObservationSubId></OBX_4_ObservationSubId><OBX_5_ObservationValue>SEE NOTE</OBX_5_ObservationValue><OBX_6_Units><CE_0_Identifier></CE_0_Identifier></OBX_6_Units><OBX_7><RangeLow></RangeLow><RangeHigh></RangeHigh><RangeAlpha></RangeAlpha></OBX_7><OBX_8_AbnormalFlags></OBX_8_AbnormalFlags><OBX_9_Probability></OBX_9_Probability><OBX_10_NatureOfAbnormalTest></OBX_10_NatureOfAbnormalTest><OBX_11_ObservationResultStatus>F</OBX_11_ObservationResultStatus><OBX_12_DateLastObsNormalValues><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBX_12_DateLastObsNormalValues><OBX_13_UserDefinedAccessChecks></OBX_13_UserDefinedAccessChecks><OBX_14_DateTimeOfTheObservation><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBX_14_DateTimeOfTheObservation><OBX_15_ProducerSId><CE_0_Identifier></CE_0_Identifier></OBX_15_ProducerSId><OBX_16_ResponsibleObserver><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBX_16_ResponsibleObserver><OBX_17_ObservationMethod><CE_0_Identifier></CE_0_Identifier></OBX_17_ObservationMethod></OBX_ObservationResultSegment><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>1</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Endometrium (Biopsy)</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><OBX_ObservationResultSegment><OBX_1_SetIdObx>2</OBX_1_SetIdObx><OBX_2_ValueType>ST</OBX_2_ValueType><OBX_3_ObservationIdentifier><CE_0_Identifier>22634-0</CE_0_Identifier><CE_1_Text></CE_1_Text><CE_2_NameOfCodingSystem>LOINC</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>90001512</CE_3_AlternateIdentifier><CE_4_AlternateText>A GROSS DESCRIPTION</CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBX_3_ObservationIdentifier><OBX_4_ObservationSubId></OBX_4_ObservationSubId><OBX_5_ObservationValue>SEE NOTE</OBX_5_ObservationValue><OBX_6_Units><CE_0_Identifier></CE_0_Identifier></OBX_6_Units><OBX_7><RangeLow></RangeLow><RangeHigh></RangeHigh><RangeAlpha></RangeAlpha></OBX_7><OBX_8_AbnormalFlags></OBX_8_AbnormalFlags><OBX_9_Probability></OBX_9_Probability><OBX_10_NatureOfAbnormalTest></OBX_10_NatureOfAbnormalTest><OBX_11_ObservationResultStatus>F</OBX_11_ObservationResultStatus><OBX_12_DateLastObsNormalValues><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBX_12_DateLastObsNormalValues><OBX_13_UserDefinedAccessChecks></OBX_13_UserDefinedAccessChecks><OBX_14_DateTimeOfTheObservation><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBX_14_DateTimeOfTheObservation><OBX_15_ProducerSId><CE_0_Identifier></CE_0_Identifier></OBX_15_ProducerSId><OBX_16_ResponsibleObserver><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBX_16_ResponsibleObserver><OBX_17_ObservationMethod><CE_0_Identifier></CE_0_Identifier></OBX_17_ObservationMethod></OBX_ObservationResultSegment><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>1</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Specimen is received in formalin, labeled with</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>2</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>the patient's name and consists of multiple</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>3</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>fragments of soft tissue aggregating to 2.0 x 2.0</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>4</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>x 0.1 cm, irregular in shape and tan-brown in</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>5</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>color. The specimen is entirely submitted in one</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>6</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>cassette.</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><OBX_ObservationResultSegment><OBX_1_SetIdObx>3</OBX_1_SetIdObx><OBX_2_ValueType>ST</OBX_2_ValueType><OBX_3_ObservationIdentifier><CE_0_Identifier>22637-3</CE_0_Identifier><CE_1_Text></CE_1_Text><CE_2_NameOfCodingSystem>LOINC</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>90001025</CE_3_AlternateIdentifier><CE_4_AlternateText>A DIAGNOSIS</CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBX_3_ObservationIdentifier><OBX_4_ObservationSubId></OBX_4_ObservationSubId><OBX_5_ObservationValue>SEE NOTE</OBX_5_ObservationValue><OBX_6_Units><CE_0_Identifier></CE_0_Identifier></OBX_6_Units><OBX_7><RangeLow></RangeLow><RangeHigh></RangeHigh><RangeAlpha></RangeAlpha></OBX_7><OBX_8_AbnormalFlags></OBX_8_AbnormalFlags><OBX_9_Probability></OBX_9_Probability><OBX_10_NatureOfAbnormalTest></OBX_10_NatureOfAbnormalTest><OBX_11_ObservationResultStatus>F</OBX_11_ObservationResultStatus><OBX_12_DateLastObsNormalValues><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBX_12_DateLastObsNormalValues><OBX_13_UserDefinedAccessChecks></OBX_13_UserDefinedAccessChecks><OBX_14_DateTimeOfTheObservation><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBX_14_DateTimeOfTheObservation><OBX_15_ProducerSId><CE_0_Identifier></CE_0_Identifier></OBX_15_ProducerSId><OBX_16_ResponsibleObserver><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBX_16_ResponsibleObserver><OBX_17_ObservationMethod><CE_0_Identifier></CE_0_Identifier></OBX_17_ObservationMethod></OBX_ObservationResultSegment><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>1</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment></NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>2</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Mainly mucus with scant fragments of benign</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>3</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>endocervix, squamous epithelium and lower uterine</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>4</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>segment tissue.</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>5</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment></NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>6</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>No endometrial tissue is present for diagnostic</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>7</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>evaluation.</NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>8</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment></NTE_3_Comment></NTE_NotesAndCommentsSegment_2><OBX_ObservationResultSegment><OBX_1_SetIdObx>4</OBX_1_SetIdObx><OBX_2_ValueType>ST</OBX_2_ValueType><OBX_3_ObservationIdentifier><CE_0_Identifier>22638-1</CE_0_Identifier><CE_1_Text></CE_1_Text><CE_2_NameOfCodingSystem>LOINC</CE_2_NameOfCodingSystem><CE_3_AlternateIdentifier>90001298</CE_3_AlternateIdentifier><CE_4_AlternateText>A COMMENT</CE_4_AlternateText><CE_5_NameOfAlternateCodingSystem>L</CE_5_NameOfAlternateCodingSystem></OBX_3_ObservationIdentifier><OBX_4_ObservationSubId></OBX_4_ObservationSubId><OBX_5_ObservationValue>SEE NOTE</OBX_5_ObservationValue><OBX_6_Units><CE_0_Identifier></CE_0_Identifier></OBX_6_Units><OBX_7><RangeLow></RangeLow><RangeHigh></RangeHigh><RangeAlpha></RangeAlpha></OBX_7><OBX_8_AbnormalFlags></OBX_8_AbnormalFlags><OBX_9_Probability></OBX_9_Probability><OBX_10_NatureOfAbnormalTest></OBX_10_NatureOfAbnormalTest><OBX_11_ObservationResultStatus>F</OBX_11_ObservationResultStatus><OBX_12_DateLastObsNormalValues><TS_0_TimeOfAnEvent></TS_0_TimeOfAnEvent></OBX_12_DateLastObsNormalValues><OBX_13_UserDefinedAccessChecks></OBX_13_UserDefinedAccessChecks><OBX_14_DateTimeOfTheObservation><TS_0_TimeOfAnEvent>201311081744</TS_0_TimeOfAnEvent></OBX_14_DateTimeOfTheObservation><OBX_15_ProducerSId><CE_0_Identifier></CE_0_Identifier></OBX_15_ProducerSId><OBX_16_ResponsibleObserver><XCN_0_IdNumberSt></XCN_0_IdNumberSt></OBX_16_ResponsibleObserver><OBX_17_ObservationMethod><CE_0_Identifier></CE_0_Identifier></OBX_17_ObservationMethod></OBX_ObservationResultSegment><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>1</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment></NTE_3_Comment></NTE_NotesAndCommentsSegment_2><NTE_NotesAndCommentsSegment_2><NTE_1_SetIdNte>2</NTE_1_SetIdNte><NTE_2_SourceOfComment></NTE_2_SourceOfComment><NTE_3_Comment>Clinical correlation/follow-up is recommended.</NTE_3_Comment></NTE_NotesAndCommentsSegment_2></ns0:ORU_R01_231_GLO_DEF>


    MBH

    Monday, September 26, 2016 8:13 PM
  • Hi jaguarjags,

    I think the key point of fetching the value from your above XML is how to handle the XML namespace. About this part, you could have a look at following demo that shows you how to do it.

    create table MyTable (Roles XML)
    
    insert into MyTable values
    ('Your XML Value')
    
    select * from mytable
    
    ;WITH XMLNAMESPACES('http://MHP.Biztalk.HL7' as ns0)
    select Roles.query('/ns0:ORU_R01_231_GLO_DEF/PID_PatientIdentificationSegment/PID_2_PatientId/CX_0_Id/text()')
    from mytable

    Then, you could refer to the following articles to learn how to query the XML value with T-SQL.

    query() Method (xml Data Type)

    https://msdn.microsoft.com/en-us/library/ms191474.aspx?f=255&MSPPError=-2147217396

    OPENXML Method and similar thread.

    https://msdn.microsoft.com/en-us/library/ms186918.aspx

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b07ea1f9-3ded-47ea-a6a7-b909e166ca74/cannot-parse-using-openxml-with-namespace?forum=transactsql

    Best Regards,

    Albert Zhang

    Tuesday, September 27, 2016 8:24 AM
  • Check this link on how to pass XML to a Stored Procedure as a parameter and how to parse it inside to a relational form: https://sqlwithmanoj.com/2012/09/09/passing-multipledynamic-values-to-stored-procedures-functions-part2-by-passing-xml/

    ~manoj | SQLwithManoj.com

    Tuesday, September 27, 2016 9:08 AM