Assistance with AdventureWorks2019 query error "XQuery [Sales.Store.Demographics.value()]: There is no element named 'StoreSurvey'"

Question

I'm playing about with the AdventureWorks2019 database in SQL server. I was trying to find the relationship between store trading duration and revenue. The majority of this information is in the Sales.Store table but the date the store opened is contained within an XML document so I looked into how to access this information and came up with this query.

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;

I had thought this would be the correct way to do it but it is giving the error in the title. For reference here is the XML

<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">
  <AnnualSales>800000</AnnualSales>
  <AnnualRevenue>80000</AnnualRevenue>
  <BankName>United Security</BankName>
  <BusinessType>BM</BusinessType>
  <YearOpened>1996</YearOpened>
  <Specialty>Mountain</Specialty>
  <SquareFeet>21000</SquareFeet>
  <Brands>2</Brands>
  <Internet>ISDN</Internet>
  <NumberEmployees>13</NumberEmployees>
</StoreSurvey>

And here is a snippet of the table

My question is why doesnt this work the way I thought it would? and could someone assist in making it work as I intended it?

Answer 1

Here is how to query, a.k.a. shred, XML data type column with the default namespace.

SQL #1

USE AdventureWorks2019;
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey')
SELECT BusinessEntityID
    , c.value('(YearOpened)[1]', 'INT') AS YearOpened
    , c.value('(BankName)[1]', 'VARCHAR(30)') AS BankName
FROM sales.Store
CROSS APPLY demographics.nodes('/StoreSurvey') AS t(c);

SQL #2

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey')
SELECT BusinessEntityID
    , demographics.value('(/StoreSurvey/YearOpened)[1]', 'INT') AS YearOpened
    , demographics.value('(/StoreSurvey/BankName)[1]', 'VARCHAR(30)') AS BankName
FROM sales.Store;

Output

BusinessEntityID	YearOpened	BankName
292	1996	United Security
294	1991	International Bank
296	1999	Primary Bank & Reserve
298	1994	International Security
300	1987	Guardian Bank