Search code examples
sqlsql-servert-sqlxqueryserilog

SQL Server XQuery Syntax Error

I'm using Serilog Sinks for SQLServer, and following DB schema and XQuery Syntax using this link. However when I run the following query in Navicat, I get error.

Here's the query:

SELECT 
Properties.value('(/properties/property[@key="contact"]/structure[@type="Contact"]/property[@key="ContactId"])[1]', 'nvarchar(max)') AS ContactId,
Properties.value('(/properties/property[@key="contact"]/structure[@type="Contact"]/property[@key="FirstName"])[1]', 'nvarchar(50)') AS FirstName,
Properties.value('(/properties/property[@key="contact"]/structure[@type="Contact"]/property[@key="Surname"])[1]', 'nvarchar(100)') AS Surname,
Properties.value('(/properties/property[@key="cacheKey"])[1]', 'nvarchar(100)') AS CacheKey,
*
FROM Log
WHERE MessageTemplate = 'Contact {@contact} added to cache with key {@cacheKey}'
    AND Properties.value('(/properties/property[@key="contact"]/structure[@type="Contact"]/property[@key="ContactId"])[1]', 'nvarchar(max)') = 'f7d10f53-4c11-44f4-8dce-d0e0e22cb6ab'

Here is the error statement:

[Err] 42000 - [SQL Server]Cannot find either column "Properties" or the user-defined function or aggregate "Properties.value", or the name is ambiguous.

Any solution or workaround would be highly appreciated.

Solution

  • Your query would run if Log table had Properties column and its format was XML.

    So, you have to change the type of a column or cast it as XML in a query.

    Second approach would look like cast(Properties as xml).value...