Reading XML Data with T-SQL

Lets say we have a below string of XML, which was sent to us via c# client, then we can perform following Tasks within our sp.

DECLARE @XmlVariable XML = '
<patientCheckinViewModel>\r\n  <patient_id>21</patient_id>\r\n  <patientInfo>\r\n    <Patients_Last_Name>Munir</Patients_Last_Name>\r\n    <Patients_First_Name>Saima</Patients_First_Name>\r\n    <Patients_Middle_Name>A</Patients_Middle_Name>\r\n    <Gender>1</Gender>\r\n    <Date_of_Birth>2015-01-15T00:00:00</Date_of_Birth>\r\n  </patientInfo>\r\n  <contactInfo>\r\n    <Patients_Home_Phone_Number>(917) 502-7105</Patients_Home_Phone_Number>\r\n    <Patients_Mobile_Phone_Number>(917) 526-3777</Patients_Mobile_Phone_Number>\r\n    <Parent_Email_Address>skhan@appliancesofamerica.com</Parent_Email_Address>\r\n    <Send_Self_Check_In_Request>false</Send_Self_Check_In_Request>\r\n    <Patients_Address_Line_1>1535A macdonald ave</Patients_Address_Line_1>\r\n    <Patients_Address_Line_2>1</Patients_Address_Line_2>\r\n    <city>Brooklyn</city>\r\n    <state>33</state>\r\n    <zip>11230</zip>\r\n  </contactInfo>\r\n  <guardianInfo>\r\n    <id>4</id>\r\n    <patient_id>21</patient_id>\r\n    <Guardian_Last_Name>khan</Guardian_Last_Name>\r\n    <Guardian_First_Name>saima</Guardian_First_Name>\r\n    <Guardian_Home_phone>7187187188</Guardian_Home_phone>\r\n    <Guardian_mobile_phone>3478745478</Guardian_mobile_phone>\r\n    <Guardian_email>skhan@appliancesofamerica.com</Guardian_email>\r\n    <Guardian_State>0</Guardian_State>\r\n    <Guardian_relation>0</Guardian_relation>\r\n    <isEmergencyContact>0</isEmergencyContact>\r\n    <Emergency_Contact_State>0</Emergency_Contact_State>\r\n  </guardianInfo>\r\n  <providerInfo>\r\n    <id>1</id>\r\n    <patient_id>21</patient_id>\r\n    <pcp_name>wahid</pcp_name>\r\n    <pharmacy>0</pharmacy>\r\n  </providerInfo>\r\n  <schoolInfo>\r\n    <id>1</id>\r\n    <patient_id>21</patient_id>\r\n    <patient_attend_school>true</patient_attend_school>\r\n    <grade_level>15</grade_level>\r\n    <patient_school_problem>false</patient_school_problem>\r\n  </schoolInfo>\r\n  <ResponsibleParty>\r\n    <id>1</id>\r\n    <patient_id>21</patient_id>\r\n    <payment_responsible>0</payment_responsible>\r\n    <party_fname>gg</party_fname>\r\n    <party_lname>gg</party_lname>\r\n    <party_state>0</party_state>\r\n  </ResponsibleParty>\r\n  <PaymentDetails>\r\n    <patient_id>21</patient_id>\r\n    <payment_method>4</payment_method>\r\n    <automatic_payment>false</automatic_payment>\r\n    <cc_name>khan</cc_name>\r\n    <cc_type>3</cc_type>\r\n    <patient_policy_holder>false</patient_policy_holder>\r\n    <patient_Insurance_Carrier>oxford</patient_Insurance_Carrier>\r\n    <nonPatient_fname>other f name</nonPatient_fname>\r\n    <nonPatient_state>0</nonPatient_state>\r\n  </PaymentDetails>\r\n  <HippaDetails>\r\n    <id>1</id>\r\n    <patient_id>21</patient_id>\r\n    <release_patient_information>false</release_patient_information>\r\n    <emeregency_contact>false</emeregency_contact>\r\n    <mark_confidential>false</mark_confidential>\r\n    <phone_gets_messages>false</phone_gets_messages>\r\n    <i_acknowlege>0</i_acknowlege>\r\n    <i_fully_aware_phone_noSecure>false</i_fully_aware_phone_noSecure>\r\n    <relations>\r\n      <Register_patient_hipaa_relations_ViewModel>\r\n        <id>3</id>\r\n        <patient_id>21</patient_id>\r\n        <relation>Lover</relation>\r\n      </Register_patient_hipaa_relations_ViewModel>\r\n      <Register_patient_hipaa_relations_ViewModel>\r\n        <id>4</id>\r\n        <patient_id>21</patient_id>\r\n        <relation>Hater</relation>\r\n      </Register_patient_hipaa_relations_ViewModel>\r\n      <Register_patient_hipaa_relations_ViewModel>\r\n        <id>5</id>\r\n        <patient_id>21</patient_id>\r\n        <relation>bbvvb</relation>\r\n      </Register_patient_hipaa_relations_ViewModel>\r\n    </relations>\r\n  </HippaDetails>\r\n  <TestMV>\r\n    <id>22</id>\r\n    <Profile>\r\n      <profile>\r\n        <fname>Eliza</fname>\r\n        <lname>Khan</lname>\r\n      </profile>\r\n      <profile>\r\n        <fname>Rida</fname>\r\n        <lname>Saqib</lname>\r\n      </profile>\r\n    </Profile>\r\n  </TestMV>\r\n</patientCheckinViewModel>'

  -- Select Columns which Loop within the XML File

  SELECT 
     XTbl.Rels.value('relation[1]', 'varchar(500)') ,
XTbl.Rels.value('id[1]', 'varchar(500)') 
  FROM      @XmlVariable.nodes('/patientCheckinViewModel/HippaDetails/relations/Register_patient_hipaa_relations_ViewModel') AS XTbl(Rels)

Select a Single Column

Select 
@data.value('(/patientCheckinViewModel/PaymentDetails/nonPatient_state)[1]', 'int' )
From @XmlVariable



Posted by:

No comments posted.

Name :  

Email :  

Comment Below