Parsing a Continuity of Care Document (CCD) with T-SQL [closed]

[seeweed]

I've figured out how to do some basic parsing of a CCD using T-SQL and thought I would share the result.If anyone knows a better way to do this, please feel free to comment. I'm particularly interested in reducing the text associated with the namespace declaration.I know I could simply remove the namespace (xmlns="urn:hl7-org:v3") from the original xml, however, I would prefer not to do that.\[code\]DECLARE @ClinicalDocumentXml xml-- Full document available from http://xreg2.nist.gov/cda-validation/downloads.htmlSET @ClinicalDocumentXml = '<?xml version="1.0" encoding="UTF-8"?><ClinicalDocument xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:hl7-org:v3 http://xreg2.nist.gov:8080/hitspValidation/schema/cdar2c32/infrastructure/cda/C32_CDA.xsd"> <realmCode code="US"/> <typeId root="2.16.840.1.113883.1.3" extension="POCD_HD000040"/> <templateId root="2.16.840.1.113883.3.27.1776" assigningAuthorityName="CDA/R2"/> <templateId root="2.16.840.1.113883.10.20.3" assigningAuthorityName="HL7/CDT Header"/> <templateId root="1.3.6.1.4.1.19376.1.5.3.1.1.1" assigningAuthorityName="IHE/PCC"/> <templateId root="2.16.840.1.113883.3.88.11.32.1" assigningAuthorityName="HITSP/C32"/> <id root="2.16.840.1.113883.3.72" extension="MU_Rev1_HITSP_C32C83_4Sections_NoInformationEntries_NoErrors" assigningAuthorityName="NIST Healthcare Project"/> <code code="34133-9" displayName="Summarization of episode note" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC"/> <title/> <effectiveTime value="http://stackoverflow.com/questions/10325060/20101026130945"/> <confidentialityCode/> <languageCode code="en-US"/> <recordTarget> <patientRole> <id root="ProviderID" extension="PatientID" assigningAuthorityName="Provider Name"/> <addr use="HP"> <streetAddressLine>First Address Line</streetAddressLine> <streetAddressLine>Next Address line</streetAddressLine> <city>CityName</city> <state>StateName</state> <postalCode>00000-0000</postalCode> <country>USA</country> </addr> <telecom value="http://stackoverflow.com/questions/10325060/tel:+1-(555)555-1212" use="HP" /> <patient> <name> <given>FirstName</given> <given>MiddleNameorInitial</given> <family>FamilyName</family> </name> <administrativeGenderCode code="F" displayName="Female" codeSystem="2.16.840.1.113883.5.1" codeSystemName="HL7 AdministrativeGender"/> <birthTime value="http://stackoverflow.com/questions/10325060/19840704"/> <maritalStatusCode code="S" displayName="Single" codeSystem="2.16.840.1.113883.5.2" codeSystemName="HL7 Marital status"/> <languageCommunication> <templateId root="2.16.840.1.113883.3.88.11.83.2" assigningAuthorityName="HITSP/C83"/> <templateId root="1.3.6.1.4.1.19376.1.5.3.1.2.1" assigningAuthorityName="IHE/PCC"/> <languageCode code="en-US"/> </languageCommunication> </patient> </patientRole> </recordTarget></ClinicalDocument>'SELECT Field.value('declare default element namespace "urn:hl7-org:v3";title[1]', 'varchar(255)') AS Title, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/id[1]/@extension', 'varchar(255)') AS InternalPatientID, Field.value('declare default element namespace "urn:hl7-org:v3";effectiveTime[1]/@value', 'varchar(255)') AS CreationDateTime, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/name[1]/name[1]', 'varchar(255)') AS FullName, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/name[1]/family[1]', 'varchar(255)') AS LastName, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/name[1]/given[1]', 'varchar(255)') AS FirstName, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/name[1]/given[2]', 'varchar(255)') AS MiddleName, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/birthTime[1]/@value', 'varchar(255)') AS DateOfBirth, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/patient[1]/administrativeGenderCode[1]/@code', 'varchar(255)') AS Gender, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/addr[1]/streetAddressLine[1]', 'varchar(255)') AS PatientAddress, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/addr[1]/streetAddressLine[2]', 'varchar(255)') AS PatientAddress2, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/addr[1]/city[1]', 'varchar(255)') AS PatientCity, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/addr[1]/state[1]', 'varchar(255)') AS PatientState, Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/addr[1]/postalCode[1]', 'varchar(255)') AS PatientZip, Replace(Field.value('declare default element namespace "urn:hl7-org:v3";recordTarget[1]/patientRole[1]/telecom[1]/@value', 'varchar(255)'), 'tel:', '') AS HomePhoneFROM @ClinicalDocumentXml.nodes('declare default element namespace "urn:hl7-org:v3"; ClinicalDocument') CCD(Field)\[/code\]