title: Extracting data from XML using OpenXML in SQL Server
body: <p>I have a xml which I want it to be extracted using OpenXML within SQL Server</p>
  
  <p>Here is the sample XML </p>
  
  <pre><code>&lt;row&gt;
    &lt;student_token&gt;7&lt;/student_token&gt;
    &lt;student_ssn&gt;552&lt;/student_ssn&gt;
    &lt;alternate_id&gt;20&lt;/alternate_id&gt;
    &lt;old_ssn xsi:nil="true" /&gt;
    &lt;alien_num xsi:nil="true" /&gt;
    &lt;last_name&gt;A&lt;/last_name&gt;
    &lt;first_name&gt;B&lt;/first_name&gt;
    &lt;middle_init xsi:nil="true" /&gt;
    &lt;drivers_license_num xsi:nil="true" /&gt;
    &lt;gpa_highschool xsi:nil="true" /&gt;
    &lt;created_dt&gt;2006-07-13T11:15:08.320&lt;/created_dt&gt;
    &lt;created_how&gt;4&lt;/created_how&gt;
    &lt;modified_dt&gt;2008-02-14T00:00:00&lt;/modified_dt&gt;
    &lt;modified_by&gt;4&lt;/modified_by&gt;
    &lt;primary_street2 xsi:nil="true" /&gt;
    &lt;primary_street3 xsi:nil="true" /&gt;
    &lt;primary_country xsi:nil="true" /&gt;
    &lt;email_address xsi:nil="true" /&gt;
    &lt;address_start_dt xsi:nil="true" /&gt;
    &lt;address_end_dt xsi:nil="true" /&gt;
    &lt;entrance_iv_dt xsi:nil="true" /&gt;
    &lt;entrance_iv_by xsi:nil="true" /&gt;
    &lt;exit_iv_dt&gt;2006-11-02T00:00:00&lt;/exit_iv_dt&gt;
    &lt;exit_iv_by&gt;156&lt;/exit_iv_by&gt;
    &lt;foreign_address_indicator&gt;N&lt;/foreign_address_indicator&gt;
    &lt;foreign_postal_code xsi:nil="true" /&gt;
    &lt;pin&gt;J27841&lt;/pin&gt;
    &lt;web_id&gt;J08614   &lt;/web_id&gt;
    &lt;prior_name xsi:nil="true" /&gt;
    &lt;orig_eps xsi:nil="true" /&gt;
    &lt;web_role&gt;STU1&lt;/web_role&gt;
    &lt;heal_limit_flag&gt;N&lt;/heal_limit_flag&gt;
    &lt;email_address_2&gt;test@test.com&lt;/email_address_2&gt;
    &lt;cellular_telephone&gt;415&lt;/cellular_telephone&gt;
    &lt;alt_loan_debt xsi:nil="true" /&gt;
    &lt;web_last_login xsi:nil="true" /&gt;
    &lt;foreign_country_code xsi:nil="true" /&gt;
    &lt;entrance_iv_dt_grad_plus xsi:nil="true" /&gt;
    &lt;entrance_iv_by_grad_plus xsi:nil="true" /&gt;
    &lt;failed_logins&gt;0&lt;/failed_logins&gt;
    &lt;hispanic xsi:nil="true" /&gt;
    &lt;race xsi:nil="true" /&gt;
    &lt;primary_phone_number_intl xsi:nil="true" /&gt;
    &lt;security_version&gt;0&lt;/security_version&gt;
    &lt;failed_challenge_response&gt;0&lt;/failed_challenge_response&gt;
    &lt;require_pin_reset xsi:nil="true" /&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p>The query should extract into 3 fields for each row </p>
  
  <ol>
  <li>FieldName </li>
  <li>FieldValue</li>
  <li>IsNull</li>
  </ol>
  
  <p>For example the first row should be</p>
  
  <ul>
  <li>FieldName = student_token - The node name would be the field name</li>
  <li>FieldValue = 7 </li>
  <li>IsNull = false - IsNull is based on the attribute  xsi:nil="true"</li>
  </ul>
  
  <p>How can I do this? Any help would be appreciated.</p>
  
  <p>Thanks</p>