Search code examples
sqlsql-serveradventureworks

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

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 table snippet

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?

Solution

  • 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