Query to output content of AllXml column of ELMAH_Error sql server table

Question

I am having trouble writing query so that I can query the content of AllXml column inside Elmah_Error table.

How can I list out all the item nodes as an output of the query. How could I write query to only list for certain item nodes?

I would like to get follow resultset:

item value

===== =====

ALL_HTTP HTTP_CONNECTION:xxxx

ALL_RAW Connection: xxxxx

I would also like to be able to filter the query by ErrorID

Content of AllXml column may look like this.

<error
  application="/"
  message="hello world"
  source="TestWebElmah"
  detail="xxxxx">
  <serverVariables>
    <item
      name="ALL_HTTP">
      <value
        string="HTTP_CONNECTION:xxxx" />
    </item>
    <item
      name="ALL_RAW">
      <value
        string="Connection: xxxxx" />
    </item>

  </serverVariables>
</error>

Answer 1

Remote Addr nodes

select T.N.value('(value/@string)[1]', 'varchar(30)') as REMOTE_ADDR
from 
(select cast(AllXml as xml) as AllXml from ELMAH_Error) e
   cross apply AllXml.nodes('//item[@name="REMOTE_ADDR"]') as T(N)

HTTP User Agents which contain mozilla

select T.N.value('(value/@string)[1]', 'varchar(30)') as HTTP_USER_AGENT
from 
(select cast(AllXml as xml) as AllXml from ELMAH_Error) e
   cross apply AllXml.nodes('//item[@name="HTTP_USER_AGENT"]') as T(N)
where T.N.value('(value/@string)[1]', 'varchar(30)') like '%mozilla%'

Elmah table stores the AllXml column as nvarchar so it needs to be casted to xml

all tags + values, by error id

select T.N.value('@name', 'varchar(30)') as Name,
       T.N.value('(value/@string)[1]', 'varchar(30)') as Value
from 
(select cast(AllXml as xml) as AllXml from ELMAH_Error where ErrorId = 'DC82172B-F2C0-48CE-8621-A60B702ECF93') e
cross apply AllXml.nodes('/error/serverVariables/item') as T(N)

Before voting down this answer, because uses most of the part of Mikael Eriksson's answer, I let you know I'll happily accept the downvotes only for this reason, since is mainly true

Answer 2

This query will give you all item nodes

select T.N.value('@name', 'varchar(30)') as Name,
       T.N.value('(value/@string)[1]', 'varchar(30)') as Value
from Elmah_Error
  cross apply AllXml.nodes('/error/serverVariables/item') as T(N)

If you want to filter on any of the values you can put that in a sub-query apply a regular where clause.

select Name, 
       Value
from
  (
    select T.N.value('@name', 'varchar(30)') as Name,
           T.N.value('(value/@string)[1]', 'varchar(30)') as Value
    from Elmah_Error
      cross apply AllXml.nodes('/error/serverVariables/item') as T(N)
  ) T
where Name = 'ALL_HTTP'