Skip to content

Instantly share code, notes, and snippets.

@atifaziz
Created August 29, 2008 08:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save atifaziz/7943 to your computer and use it in GitHub Desktop.
Save atifaziz/7943 to your computer and use it in GitHub Desktop.
/*
Demonstrates how to sanitize and extract values from the AllXml column
as additional columns for data mining and reporting purposes.
See also "Additional Reporting" discussion thread at:
http://groups.google.com/group/elmah/t/28d8fce91a407494
*/
SELECT
[ErrorId],
[Application],
[Host],
[Type],
[Source],
[Message],
[User],
[StatusCode],
[TimeUtc],
[Sequence],
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="REMOTE_ADDR"]
/value
/@string)[1]',
'varchar(20)'), N'')
REMOTE_ADDR,
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="HTTP_USER_AGENT"]
/value
/@string)[1]',
'nvarchar(250)'), N'')
HTTP_USER_AGENT,
ISNULL([AllXml].value('(
/error
/serverVariables
/item[@name="HTTP_REFERER"]
/value
/@string)[1]',
'nvarchar(2000)'), N'')
HTTP_REFERER
FROM (
SELECT
[ErrorId],
[Application],
[Host],
[Type],
[Source],
[Message],
[User],
[StatusCode],
[TimeUtc],
[Sequence],
CAST(
-- Remove characters invalid in XML
REPLACE(CAST([AllXml] AS NVARCHAR(MAX)), CHAR(0), '?')
AS XML) [AllXml]
FROM
[ELMAH_Error]) AS [Error]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment