如何修改SQL Server查询，使AttributeXml的@ID匹配v1.Id

需要修改SQL Server查询，让ProductAttributeCombination表的AttributeXml列中的@ID值匹配v1.Id（ProductAttributeValue表的Id）。当前关联该表后无结果，移除关联则正常返回数据。

现有信息 #

• ProductAttributeCombination.AttributeXml为nvarchar(Max)类型，现有XML条件：

CAST(c1.AttributesXml AS XML).exist('/Attributes/ProductAttribute[@ID=sql:variable("@v1Id") and ProductAttributeValue/Value[. = sql:variable("@v1Id") ]') = 1

• 完整查询：

SELECT 
p.Id, 
p.Sku, 
p.[Name], 
v1.[Name] AS Color, 
v2.[Name] AS Size
 , c1.StockQuantity
FROM  Product p
INNER JOIN Product_ProductAttribute_Mapping m1 ON m1.ProductId = p.Id AND p.Deleted = '0' 
INNER JOIN Product_ProductAttribute_Mapping m2 ON m2.ProductId = p.Id 
INNER JOIN ProductAttribute a1 ON a1.Id = m1.ProductAttributeId AND a1.[Name] = 'Color'
INNER JOIN ProductAttribute a2 ON a2.Id = m2.ProductAttributeId AND a2.[Name] = 'Size'
INNER JOIN ProductAttributeValue v1 ON v1.ProductAttributeMappingId = m1.Id
INNER JOIN ProductAttributeValue v2 ON v2.ProductAttributeMappingId = m2.Id
  INNER JOIN ProductAttributeCombination c1 ON c1.ProductId = p.Id 
        AND CAST(c1.AttributesXml AS XML).exist('/Attributes/ProductAttribute[@ID=sql:variable("@v1Id") and ProductAttributeValue/Value[. = sql:variable("@v1Id") ]') = 1

• AttributeXml示例：

<Attributes><ProductAttribute ID="59"><ProductAttributeValue><Value>112</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="60"><ProductAttributeValue><Value>115</Value></ProductAttributeValue></ProductAttribute></Attributes>

• 已知规则：
  • ProductAttribute的ID等于Product_ProductAttribute_Mapping.Id
  • ProductAttributeValue的Value等于ProductAttributeValue.Id

问题根源 #

现有XML条件存在两个核心错误：

1. 错误地用同一个@v1Id同时匹配ProductAttribute的@ID和ProductAttributeValue/Value，但根据已知规则，前者对应m1.Id（属性映射ID），后者对应v1.Id（属性值ID），二者是完全不同的字段值；
2. 仅匹配了Color属性，没有同时匹配Size属性的对应条件，导致无法关联到包含完整属性组合的记录。

修正后的查询 #

SELECT 
    p.Id, 
    p.Sku, 
    p.[Name], 
    v1.[Name] AS Color, 
    v2.[Name] AS Size,
    c1.StockQuantity
FROM  Product p
INNER JOIN Product_ProductAttribute_Mapping m1 
    ON m1.ProductId = p.Id AND p.Deleted = '0' 
INNER JOIN Product_ProductAttribute_Mapping m2 
    ON m2.ProductId = p.Id 
INNER JOIN ProductAttribute a1 
    ON a1.Id = m1.ProductAttributeId AND a1.[Name] = 'Color'
INNER JOIN ProductAttribute a2 
    ON a2.Id = m2.ProductAttributeId AND a2.[Name] = 'Size'
INNER JOIN ProductAttributeValue v1 
    ON v1.ProductAttributeMappingId = m1.Id
INNER JOIN ProductAttributeValue v2 
    ON v2.ProductAttributeMappingId = m2.Id
INNER JOIN ProductAttributeCombination c1 
    ON c1.ProductId = p.Id 
    AND CAST(c1.AttributesXml AS XML).exist('/Attributes/ProductAttribute[@ID=sql:variable("@m1Id") and ProductAttributeValue/Value[. = sql:variable("@v1Id")]]') = 1
    AND CAST(c1.AttributesXml AS XML).exist('/Attributes/ProductAttribute[@ID=sql:variable("@m2Id") and ProductAttributeValue/Value[. = sql:variable("@v2Id")]]') = 1

关键说明 #

1. 拆分匹配条件：用m1.Id（属性映射ID）匹配Color对应的ProductAttribute的@ID，用v1.Id（属性值ID）匹配Color对应的Value；
2. 新增Size的匹配逻辑：用m2.Id匹配Size对应的ProductAttribute的@ID，用v2.Id匹配Size对应的Value；
3. 两个exist条件同时生效，确保关联到同时包含正确Color和Size属性组合的ProductAttributeCombination记录。

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