基于经典.NET Framework，使用Serilog从SQL Server读取日志属性值时的SQL语法错误解决方案求助

Let's work through this step by step to fix your error and get the XML property values you need:

1. The Root Causes of Your SQL Error #

Your query is throwing a syntax error for two key reasons:

• Mismatched XPath structure: Your XML uses <root> as the top-level node and <prop> for child elements, but your query references /properties/property which doesn't match your actual XML.
• Malformed value() function syntax: The value() method requires the XPath expression to be properly enclosed, and the index [1] (to target the first matching element) should be part of the XPath, not outside the parentheses. Additionally, SQL requires escaping inner single quotes with a second single quote.

2. Corrected SQL Query #

Here's the fixed SELECT statement that matches your XML structure and uses valid XQuery syntax:

SELECT Properties.value('(/root/prop[@key=''FirstName''])[1]', 'nvarchar(max)') AS FName 
FROM EventLog

Key Fixes Breakdown: #

• Updated the XPath to /root/prop[@key='FirstName'] to align with your XML's node names
• Moved [1] inside the XPath expression to correctly target the first matching <prop> element
• Escaped the inner single quote in @key=''FirstName'' (SQL uses two single quotes to represent one literal single quote)

3. Updated C# Code #

I've also refined your C# code with best practices to ensure proper resource disposal and avoid null reference issues:

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    string query = @"SELECT Properties.value('(/root/prop[@key=''FirstName''])[1]', 'nvarchar(max)') AS FName FROM EventLog";
    
    // Use using blocks to auto-dispose command and reader resources
    using (SqlCommand comm = new SqlCommand(query, con))
    using (SqlDataReader srd = comm.ExecuteReader())
    {
        while (srd.Read())
        {
            // Add null checking to avoid exceptions if the property is missing
            string firstName = srd["FName"]?.ToString() ?? string.Empty;
            // Do something with the extracted first name here
        }
    }
    // No need for explicit con.Close() - the outer using block handles this automatically
}

4. Extracting Other Properties #

To pull other values like LastName or BirthDate, just adjust the XPath in the query:

-- Extract LastName
SELECT Properties.value('(/root/prop[@key=''LastName''])[1]', 'nvarchar(max)') AS LName FROM EventLog

-- Extract BirthDate (cast to datetime for proper date handling)
SELECT Properties.value('(/root/prop[@key=''BirthDate''])[1]', 'datetime') AS BirthDate FROM EventLog

内容的提问来源于stack exchange，提问作者salma baian