How to extract values with a specific data from an XML column?

Question

I have a query that gave an error that i dont know exactly why gave that error. What is the problem?

   SELECT top 10 [ID]
      ,[EVENTCLASS]
      ,[EVENTNAME]
      ,[EVENTDATA]
      ,[EVENTDATE]
      ,[BYUSER]
      ,[IDENTIFIER]
      ,[ZONE]
      ,[ARCHIVED]
      , metadata.value('(./@Value)[1]', 'nvarchar(max)') as CurrentEvent
FROM [dbo].[EventLog] with(nolock)
  cross apply eventdata.nodes ('/document/data[@name = ''EventStatus'']') as metadata
  where convert (date, eventdate) > '2019-04-15'
  and  metadata.value('(./@value)[1]', 'nvarchar(max)') = 'Recognized'

I have to search in Eventdata column that is an XML like this :

<Document ID="50f1c559-7a2a-4420-8fcb-de1e3d523c1a" Action="CREATE">
  <Data Name="EventTenant" Value="soc" Type="System.String" />
  <Data Name="TargetTenant" Value="soc" Type="System.String" />
  <Data Name="UserId" Value="519" Type="System.Int32" />
  <Data Name="EventStatus" Value="Recognized" Type="System.String" />
  <Data Name="TimeStamp" Value="2019-03-15 12:22:02.095" Type="System.String" />
  <Data Name="NextEventStatus" Value="Exported" Type="System.String" />
  <Data Name="NextEventAppId" Value="003" Type="System.String" />
  <Data Name="DocumentId" Value="50f1c559-7a2a-4420-8fcb-de1e3d523c1a,003" Type="System.String" />
</Document>

I want to search by EventStatus and its value.

XML is case-sensitive...if you're using XPath: «document/data» s/b «Document/Data»...and also «@name=''EventStatus''» s/b «@Name='EventStatus'»...apart from those there are some other errors are there too... — Cylian
– Cylian, Commented May 6, 2019 at 6:17

Mikael Eriksson · Accepted Answer · 2019-05-06 06:57:52Z

2

You are shredding on nodes where Name=EventStatus and you use a where clause where Value=Recognized.

Those can be combined into one using exist something like this.

SELECT top 10 [ID]
      ,[EVENTCLASS]
      ,[EVENTNAME]
      ,[EVENTDATA]
      ,[EVENTDATE]
      ,[BYUSER]
      ,[IDENTIFIER]
      ,[ZONE]
      ,[ARCHIVED]
FROM [dbo].[EventLog]
where convert (date, eventdate) > '2019-04-15'
  and eventdata.exist('/Document/Data[@Name = "EventStatus" and @Value = "Recognized"]') = 1

answered May 6, 2019 at 6:57

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract values with a specific data from an XML column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related