AdventureWorks2019 XQuery报错：不存在'StoreSurvey'元素，求修复

报错原因 #

你的XML文档根元素StoreSurvey带有命名空间http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey，但原查询的XQuery未指定该命名空间，导致SQL Server无法识别带命名空间的元素，因此抛出"There is no element named 'StoreSurvey'"错误。

修改后的查询方案 #

方案1：显式声明XML命名空间（推荐） #

使用WITH XMLNAMESPACES语句指定默认命名空间，让XQuery能正确识别XML元素：

CREATE VIEW store_duration_revenue AS
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey'),
sub AS
(
  SELECT s.Name AS StoreName, 
         s.Demographics.value ('(/StoreSurvey/YearOpened)[1]', 'int') AS YearOpened, 
         YEAR(s.ModifiedDate) - s.Demographics.value ('(/StoreSurvey/YearOpened)[1]', 'int') AS TradingDuration, 
         soh.TotalDue
  FROM Sales.Store AS s
  JOIN Sales.Customer AS c ON s.BusinessEntityID = c.StoreID
  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
)
SELECT StoreName, YearOpened, TradingDuration, SUM(TotalDue) AS Revenue
FROM sub
GROUP BY StoreName, YearOpened, TradingDuration;

方案2：使用通配符忽略命名空间 #

如果不需要严格指定命名空间，可以用*:通配符匹配任意命名空间下的元素：

CREATE VIEW store_duration_revenue AS
WITH sub AS
(
  SELECT s.Name AS StoreName, 
         s.Demographics.value ('(/*:StoreSurvey/*:YearOpened)[1]', 'int') AS YearOpened, 
         YEAR(s.ModifiedDate) - s.Demographics.value ('(/*:StoreSurvey/*:YearOpened)[1]', 'int') AS TradingDuration, 
         soh.TotalDue
  FROM Sales.Store AS s
  JOIN Sales.Customer AS c ON s.BusinessEntityID = c.StoreID
  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
)
SELECT StoreName, YearOpened, TradingDuration, SUM(TotalDue) AS Revenue
FROM sub
GROUP BY StoreName, YearOpened, TradingDuration;

方案说明 #

• 方案1更符合XML规范，当命名空间固定时推荐使用，避免后续因命名空间变更引发问题。
• 方案2适合临时查询或命名空间不明确的场景，写法更简洁，但可读性稍差。

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