基于xml.nodes的简单T-SQL查询失效，附XML代码求助

Hey there! Let's break down why your xml.nodes query isn't behaving as expected—this is almost always tied to the XML namespaces in your document, which are easy to miss but essential for querying XML correctly in SQL Server.

The Core Issue: Unhandled XML Namespaces #

Your XML has two distinct namespaces you haven't accounted for:

1. A default namespace for the root asset_market_data_response element: http://schemas.bcs.ru/marketing_data_service/in/
2. A dedicated namespace for the system_block element and its children: http://schemas.bcs.ru/is/clipboard/

When you don't explicitly declare and use these namespaces in your T-SQL query, SQL Server can't match the nodes you're trying to target—it treats them as non-existent, leading to empty results or errors.

Fix: Declare Namespaces and Target Nodes Correctly #

Here's a working example that addresses the namespace issue. We'll use WITH XMLNAMESPACES to define the namespaces, then reference them in our nodes and value methods:

DECLARE @xml xml = Cast('<asset_market_data_response xmlns="http://schemas.bcs.ru/marketing_data_service/in/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
<system_block xmlns="http://schemas.bcs.ru/is/clipboard/"> 
<originator>Bloomberg</originator> 
<mean_for>IS.Clipboard</mean_for> 
<document_id>e7aa2033-0a53-4390-a09b-504673ea54bb</document_id> 
<event>New</event>
</system_block>
</asset_market_data_response>' AS XML);

-- Declare all required XML namespaces
WITH XMLNAMESPACES(
    DEFAULT 'http://schemas.bcs.ru/marketing_data_service/in/', -- Default namespace for root element
    'http://schemas.bcs.ru/is/clipboard/' AS cb -- Prefix for system_block's namespace
)
SELECT
    -- Extract values using the namespace prefix for system_block children
    system_block.value('(cb:originator)[1]', 'NVARCHAR(100)') AS Originator,
    system_block.value('(cb:mean_for)[1]', 'NVARCHAR(100)') AS MeanFor,
    system_block.value('(cb:document_id)[1]', 'UNIQUEIDENTIFIER') AS DocumentID,
    system_block.value('(cb:event)[1]', 'NVARCHAR(50)') AS Event
FROM @xml.nodes('asset_market_data_response/cb:system_block') AS T(system_block);

Key Notes to Remember: #

• Always declare namespaces: Use WITH XMLNAMESPACES to define every namespace present in your XML. For default namespaces, use the DEFAULT keyword; for others, assign a short prefix (like cb here) for easy reference.
• Prefix namespace-specific nodes: Any element that belongs to a non-default namespace (like system_block and its children) must be prefixed with the namespace alias you defined.
• Validate your XML: Ensure your full XML document is well-formed (the ... in your snippet suggests it's truncated—make sure there are no unclosed tags or syntax errors).

If you had a different target node in mind, just adjust the path in nodes and the value method calls while keeping the namespace prefixes intact, and it should work as expected.

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