Need a query to retrieve the data from XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need a query to retrieve the data from XML

Author

Topic

Jampandu
Starting Member

12 Posts

Posted - 2014-09-09 : 02:06:38

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>

I 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!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-09 : 08:17:15

Here's something to get you started:


;
with xmlnamespaces('urn:h12-org:v3' as ns)
select x.value('ns:patientRole[1]/ns:id[1]/@extension[1]', 'nvarchar(50)')					as extension
	 , x.value('ns:patientRole[1]/ns:patient[1]/ns:name[1]/ns:given[1]', 'nvarchar(50)')	as GivenName
	 , x.value('ns:patientRole[1]/ns:patient[1]/ns:name[1]/ns:family[1]', 'nvarchar(50)')	as FamilyName
	 , x.value('ns:patientRole[1]/ns:addr[1]/ns:streetAddressLine[1]', 'nvarchar(50)')		as [Address]
	 , x.value('ns:patientRole[1]/ns:telecom[1]/@value', 'nvarchar(50)')					as telephone
from @xml.nodes('/ns:ClinicalDocument/ns:recordTarget') n(x)

The rest of the values can be extracted in a similar fashion

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 08:35:46

even easier:


;
with xmlnamespaces(default 'urn:h12-org:v3' )
select x.value('patientRole[1]/id[1]/@extension[1]', 'nvarchar(50)')			as extension
	 , x.value('patientRole[1]/patient[1]/name[1]/given[1]', 'nvarchar(50)')	as GivenName
	 , x.value('patientRole[1]/patient[1]/name[1]/family[1]', 'nvarchar(50)')	as FamilyName
	 , x.value('patientRole[1]/addr[1]/streetAddressLine[1]', 'nvarchar(50)')	as [Address]
	 , x.value('patientRole[1]/telecom[1]/@value', 'nvarchar(50)')				as telephone
from @xml.nodes('/ClinicalDocument/recordTarget') n(x)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources