In the following example, ExceptionLog table has an xml column called TMXML. This contains a canonical xml message. The SQL can “peer” into the xml column, and pull out selected fields.

<pre>
SELECT
  eventDateTime,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Source"][1]/requestType[1]',
  'varchar(24)') AS RequestType,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Itinerary"][1]/currentStepName[1]',
  'varchar(24)') AS CurrentStepName,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Itinerary"][1]/currentProcessCode[1]',
  'varchar(24)') AS CurrentProcessCode
FROM ExceptionLog

</pre>

A nifty idea is to put the above SQL in a view, and hide the xquery statements from the common user. Then the user can simply do

<pre>
Select * from ViewExceptionLog
</pre>

NOTE 1: The xpath must return a single value, else you will get an error something like this:

<pre>
XQuery [TMXML.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
</pre>

This is why each element in the hierarchy has the [1] after it, to return only the first nonoccurence. Even if you data doesn’t have multiple of them, T-SQL doesn’t know, you have to be explicit.

NOTE 2: if you are copying your xpath from C#, it’s probably easier to replace the single quotes with double quotes, as C# and SQL use the opposite quotes delimiters by default.

NOTE 3: Use a tool like http://www.xpathtester.com/xpath to test your xpath outside of SQL. You can paste your xml text message there, and play with different xpath statements. Then when you have one that works, go back and plug it into your T-SQL query.

For further info: http://msdn.microsoft.com/en-us/library/ms178030.aspx