title: SQL Server query xml attribute for an element value
body: <p>Sorry if this is somewhere else, I have found a lot of similar examples but I have been unable to get it working with my data. 2 days later and I need an answer :(</p>
  
  <p>Basically have a SQL Server table with a column containing XML data. This data contains values I need to extract.</p>
  
  <p>Here is my XML.</p>
  
  <pre><code>  &lt;CustomFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd"&gt;
      &lt;Field fieldName="AutoCategory"&gt;Event Log&lt;/Field&gt;
      &lt;Field fieldName="SType"&gt;Event Log&lt;/Field&gt;
      &lt;Field fieldName="STag1"&gt;AgentGuid&lt;/Field&gt;
      &lt;Field fieldName="STag2"&gt;AlertRegistrationId&lt;/Field&gt;
      &lt;Field fieldName="STag3"&gt;LogType&lt;/Field&gt;
      &lt;Field fieldName="SValue1"&gt;619764177412541&lt;/Field&gt;
      &lt;Field fieldName="SValue2"&gt;104&lt;/Field&gt;
      &lt;Field fieldName="SValue3"&gt;1380569194&lt;/Field&gt;
      &lt;Field fieldName="SdTicketId"&gt;RPSv1006330&lt;/Field&gt;
      &lt;Field fieldName="AgentName"&gt;bla bla&lt;/Field&gt;
      &lt;Field fieldName="MachineGroupGuid"&gt;86115414719112271316891312&lt;/Field&gt;
      &lt;Field fieldName="OrgFk"&gt;59165166782128125214185317&lt;/Field&gt;
      &lt;Field fieldName="GuidAgent"&gt;619764177412541&lt;/Field&gt;
      &lt;Field fieldName="AlertCount"&gt;0&lt;/Field&gt;
      &lt;Field fieldName="TicketTitle"&gt;bla bla&lt;/Field&gt;
      &lt;Field fieldName="LegacyId"&gt;152262&lt;/Field&gt;
      &lt;Field fieldName="LegacyRef"&gt;152262&lt;/Field&gt;
      &lt;Field fieldName="CwStatus"&gt;2&lt;/Field&gt;
      &lt;Field fieldName="CwTicketId"&gt;89495&lt;/Field&gt;
  &lt;/CustomFields&gt;
  </code></pre>
  
  <p>I need to be able to pull out the value associated to the <code>CwTicketId</code> field name.</p>
  
  <p>So in essence, I want to look through the XML to find the node with <code>fieldName = "CwTicketId"</code> and to return <code>89495</code> or equivalent value.</p>
  
  <p>Below is the code I have come up with myself, which pulls values out, but the problem is sometimes the XML is ordered differently, so the values are not always on the line that I have specified, hence it returns in accurate data.</p>
  
  <pre><code>;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
  SELECT 
      ref as ServiceDeskID, 
      sdSummary as ServiceDeskSummary,
      customFields.value('(/CustomFields/Field/node())[17]', 'varchar(100)') as LegacyIDTicketing,
      customFields.value('(/CustomFields/Field/node())[19]', 'varchar(100)') as CWIDTicketing
  FROM 
      [ksubscribers].[kasadmin].[SDIncident]
  </code></pre>
  
  <p>The second value I also need, but if i can figure out how to pull one value out, I can duplicate for the other.</p>
  
  <p>Hope someone can help as I have started ripping my hair out!</p>
  
  <p>Thanks for the help!!</p>