Need a query to retrieve the data from XML

Question

Post reply

Need a query to retrieve the data from XML

Nagaram

SSCrazy

Points: 2110
More actions
September 9, 2014 at 12:08 am

#293009

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

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

FROM @xml.nodes(N'/ClinicalDocument/recordTarget/patientRole') a(b)

hope it helps

Nagaram SSCrazy Points: 2110 More actions · Answer 2

Thank you Twin.

yes, It will help me . I will try to write a query as per requirement.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

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

Nagaram SSCrazy Points: 2110 More actions · Answer 4

Nagaram

SSCrazy

Points: 2110

September 9, 2014 at 3:47 am

#1744060

Thank you Eirikur for the optimized solution.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

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