September 9, 2014 at 12:08 am
Hi Experts,
Here is the sample xml
<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
<code code="34133-9" displayName="aa aa dff fff" codeSystemName="adf" codeSystem="2.16.840.1.113883.6.1" />
<title>aaaaaaaaaaaaa</title>
<effectiveTime value="20131022133851-0700" />
<confidentialityCode code="N" codeSystem="2.16.840.1.113883.5.25" />
<languageCode code="en-US" />
<recordTarget>
<patientRole>
<id extension="ML00000038" root="226ff310-3b1f-11e3-a969-005056bb0109" />
<addr use="HH">
<streetAddressLine>601 W 1ST</streetAddressLine>
<city>SPOKANE</city>
<state>WA</state>
<postalCode>99201</postalCode>
</addr>
<telecom value="(123)234-8888" use="HP" />
<patient>
<name>
<given>DTS5.66</given>
<family>TEST</family>
</name>
<administrativeGenderCode code="F" displayName="Female" codeSystemName="AdministrativeGender" codeSystem="2.16.840.1.113883.5.1" />
<birthTime value="19760502" />
<maritalStatusCode code="S" displayName="Never Married" codeSystemName="Marital Status Value Set" codeSystem="2.16.840.1.113883.1.11.12212" />
<raceCode code="2106-3" displayName="White" codeSystemName="Race Category" codeSystem="2.16.840.1.114222.4.11.836" />
<ethnicGroupCode code="2186-5" displayName="Not Hispanic or Latino" codeSystem="2.16.840.1.114222.4.11.3015" codeSystemName="Ethnicity Group Including Unknown" />
<languageCommunication>
<languageCode code="eng" />
<preferenceInd value="true" />
</languageCommunication>
</patient>
</patientRole>
</recordTarget>
</ClinicalDocument>
Need to retrieve below information.
GivenName
FamilyName
Address
telephone
Gender
GendercodeSystemName
birthTime
Marital Status
Marital StatuscodeSystemName
Race
RacecodeSystemName
Ethnic Group
Ethnic GroupcodeSystemName
Language
LanguagecodeSystemName
Can you please guide me on how to write a sql query. or if you provide me the sql query it is really helpful.
Thanks!
September 9, 2014 at 1:22 am
just to give you idea:
Declare @Xml as xml = Replace(
'<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
<code code="34133-9" displayName="aa aa dff fff" codeSystemName="adf" codeSystem="2.16.840.1.113883.6.1" />
<title>aaaaaaaaaaaaa</title>
<effectiveTime value="20131022133851-0700" />
<confidentialityCode code="N" codeSystem="2.16.840.1.113883.5.25" />
<languageCode code="en-US" />
<recordTarget>
<patientRole>
<id extension="ML00000038" root="226ff310-3b1f-11e3-a969-005056bb0109" />
<addr use="HH">
<streetAddressLine>601 W 1ST</streetAddressLine>
<city>SPOKANE</city>
<state>WA</state>
<postalCode>99201</postalCode>
</addr>
<telecom value="(123)234-8888" use="HP" />
<patient>
<name>
<given>DTS5.66</given>
<family>TEST</family>
</name>
<administrativeGenderCode code="F" displayName="Female" codeSystemName="AdministrativeGender" codeSystem="2.16.840.1.113883.5.1" />
<birthTime value="19760502" />
<maritalStatusCode code="S" displayName="Never Married" codeSystemName="Marital Status Value Set" codeSystem="2.16.840.1.113883.1.11.12212" />
<raceCode code="2106-3" displayName="White" codeSystemName="Race Category" codeSystem="2.16.840.1.114222.4.11.836" />
<ethnicGroupCode code="2186-5" displayName="Not Hispanic or Latino" codeSystem="2.16.840.1.114222.4.11.3015" codeSystemName="Ethnicity Group Including Unknown" />
<languageCommunication>
<languageCode code="eng" />
<preferenceInd value="true" />
</languageCommunication>
</patient>
</patientRole>
</recordTarget>
</ClinicalDocument>'
, '<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">', '<ClinicalDocument>')
SELECT
a.b.value(N'patient[1]/name[1]/given[1]','varchar(50)')AS given
, a.b.value(N'patient[1]/name[1]/family[1]','varchar(50)')AS Family
, a.b.value(N'telecom[1]/@value','varchar(50)')AS telephone
, a.b.value(N'patient[1]/administrativeGenderCode[1]/@displayName','varchar(50)')AS Gender
, a.b.value(N'patient[1]/administrativeGenderCode[1]/@displayName','varchar(50)')AS Gender
FROM @xml.nodes(N'/ClinicalDocument/recordTarget/patientRole') a(b)
hope it helps
September 9, 2014 at 1:53 am
Thank you Twin.
yes, It will help me . I will try to write a query as per requirement.
September 9, 2014 at 3:38 am
Quick note, cannot recommend a string manipulation of the XML, rather use the XMLNAMESPACES directive, here is a quick parsing demo, note that the datatype specification in the value functions should probably be changed to the appropriate target data type.
😎
USE tempdb;
GO
DECLARE @TXML XML = '<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
<code code="34133-9" displayName="aa aa dff fff" codeSystemName="adf" codeSystem="2.16.840.1.113883.6.1" />
<title>aaaaaaaaaaaaa</title>
<effectiveTime value="20131022133851-0700" />
<confidentialityCode code="N" codeSystem="2.16.840.1.113883.5.25" />
<languageCode code="en-US" />
<recordTarget>
<patientRole>
<id extension="ML00000038" root="226ff310-3b1f-11e3-a969-005056bb0109" />
<addr use="HH">
<streetAddressLine>601 W 1ST</streetAddressLine>
<city>SPOKANE</city>
<state>WA</state>
<postalCode>99201</postalCode>
</addr>
<telecom value="(123)234-8888" use="HP" />
<patient>
<name>
<given>DTS5.66</given>
<family>TEST</family>
</name>
<administrativeGenderCode code="F" displayName="Female" codeSystemName="AdministrativeGender" codeSystem="2.16.840.1.113883.5.1" />
<birthTime value="19760502" />
<maritalStatusCode code="S" displayName="Never Married" codeSystemName="Marital Status Value Set" codeSystem="2.16.840.1.113883.1.11.12212" />
<raceCode code="2106-3" displayName="White" codeSystemName="Race Category" codeSystem="2.16.840.1.114222.4.11.836" />
<ethnicGroupCode code="2186-5" displayName="Not Hispanic or Latino" codeSystem="2.16.840.1.114222.4.11.3015" codeSystemName="Ethnicity Group Including Unknown" />
<languageCommunication>
<languageCode code="eng" />
<preferenceInd value="true" />
</languageCommunication>
</patient>
</patientRole>
</recordTarget>
</ClinicalDocument>';
;WITH XMLNAMESPACES ( 'urn:h12-org:abcd' AS sdtc
,DEFAULT 'urn:h12-org:v3' )
SELECT
CLINICALdocument.DATA.value('id[1]/@root','VARCHAR(50)') AS CLDOC_ID
,PATIENT_NAME.DATA.value('given[1]','VARCHAR(50)') AS GIVEN_NAME
,PATIENT_NAME.DATA.value('family[1]','VARCHAR(50)') AS FAMILY_NAME
,PATIENT.DATA.value('administrativeGenderCode[1]/@code','VARCHAR(50)') AS ADM_GENC_code
,PATIENT.DATA.value('administrativeGenderCode[1]/@displayName','VARCHAR(50)') AS ADM_GENC_displayName
,PATIENT.DATA.value('administrativeGenderCode[1]/@codeSystemName','VARCHAR(50)') AS ADM_GENC_codeSystemName
,PATIENT.DATA.value('administrativeGenderCode[1]/@codeSystem','VARCHAR(50)') AS ADM_GENC_codeSystem
,PATIENT.DATA.value('birthTime[1]/@value','VARCHAR(50)') AS birthTime
,PATIENT.DATA.value('maritalStatusCode[1]/@code','VARCHAR(50)') AS MSC_code
,PATIENT.DATA.value('maritalStatusCode[1]/@displayName','VARCHAR(50)') AS MSC_displayName
,PATIENT.DATA.value('maritalStatusCode[1]/@codeSystemName','VARCHAR(50)') AS MSC_codeSystemName
,PATIENT.DATA.value('maritalStatusCode[1]/@codeSystem','VARCHAR(50)') AS MSC_codeSystem
,PATIENT.DATA.value('raceCode[1]/@code','VARCHAR(50)') AS RCEC_code
,PATIENT.DATA.value('raceCode[1]/@displayName','VARCHAR(50)') AS RCEC_displayName
,PATIENT.DATA.value('raceCode[1]/@codeSystemName','VARCHAR(50)') AS RCEC_codeSystemName
,PATIENT.DATA.value('raceCode[1]/@codeSystem','VARCHAR(50)') AS RCEC_codeSystem
,PATIENT.DATA.value('ethnicGroupCode[1]/@code','VARCHAR(50)') AS ETHGC_code
,PATIENT.DATA.value('ethnicGroupCode[1]/@displayName','VARCHAR(50)') AS ETHGC_displayName
,PATIENT.DATA.value('ethnicGroupCode[1]/@codeSystemName','VARCHAR(50)') AS ETHGC_codeSystemName
,PATIENT.DATA.value('ethnicGroupCode[1]/@codeSystem','VARCHAR(50)') AS ETHGC_codeSystem
,LANGUAGECOMMUNICATION.DATA.value('languageCode[1]/@code','VARCHAR(50)') AS LNGCOMCR_languageCode
,LANGUAGECOMMUNICATION.DATA.value('preferenceInd[1]/@value','VARCHAR(50)') AS LNGCOMCR_preferenceInd
FROM @TXML.nodes('ClinicalDocument') AS CLINICALDOCUMENT(DATA)
OUTER APPLY CLINICALdocument.DATA.nodes('recordTarget/patientRole/patient') AS PATIENT(DATA)
OUTER APPLY PATIENT.DATA.nodes('name') AS PATIENT_NAME(DATA)
OUTER APPLY PATIENT.DATA.nodes('languageCommunication') AS LANGUAGECOMMUNICATION(DATA)
Results
CLDOC_ID GIVEN_NAME FAMILY_NAME ADM_GENC_code ADM_GENC_displayName ADM_GENC_codeSystemName ADM_GENC_codeSystem birthTime MSC_code MSC_displayName MSC_codeSystemName MSC_codeSystem RCEC_code RCEC_displayName RCEC_codeSystemName RCEC_codeSystem ETHGC_code ETHGC_displayName ETHGC_codeSystemName ETHGC_codeSystem LNGCOMCR_languageCode LNGCOMCR_preferenceInd
------------------------------------- ----------- ------------ -------------- --------------------- ------------------------ ---------------------- ---------- --------- ---------------- ------------------------- ----------------------------- ---------- ----------------- -------------------- --------------------------- ----------- ----------------------- ---------------------------------- ---------------------------- ---------------------- -----------------------
226ff30f-3b1f-11e3-a969-005056bb0109 DTS5.66 TEST F Female AdministrativeGender 2.16.840.1.113883.5.1 19760502 S Never Married Marital Status Value Set 2.16.840.1.113883.1.11.12212 2106-3 White Race Category 2.16.840.1.114222.4.11.836 2186-5 Not Hispanic or Latino Ethnicity Group Including Unknown 2.16.840.1.114222.4.11.3015 eng true
September 9, 2014 at 3:47 am
Thank you Eirikur for the optimized solution.
September 9, 2014 at 4:52 am
Excellent solution shared by Eirikur. i should avoid sharing not recommended solution in the future.
Just to let you know and other information regarding the namespace. Please do make sure XML Namespaces do not change, if you want to automate this working which i suppose you will do.
USE Tempdb
GO
-- Step 1: setup a temporary table variable.
declare @xml_table as table (
row_id tinyint identity primary key,
xml_data xml
)
-- Step 2: populate some examples with different namespaces.
insert into @xml_table(xml_data)
values('<top_level xmlns="namespace1"><snack>Garden Salsa Sun Chips</snack></top_level>')
insert into @xml_table(xml_data)
values('<top_level xmlns="another_namespace"><snack>Peanuts</snack></top_level>')
insert into @xml_table(xml_data)
values('<top_level><snack>Munchkins</snack></top_level>')
-- Step 3: return all snacks regardless of namespace
;with xmlnamespaces( default 'another_namespace'
)
select
xt.*,
xt.xml_data.value('(/top_level/snack)[1]','varchar(100)') as namespace_snack,
xt.xml_data.value('(/*:top_level/*:snack)[1]','varchar(100)') as all_snacks
from @xml_table xt
just to give you an idea what can went wrong if this happens. hope it helps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply