Xquery method query() not working in SQL Server

Question

I have a XML column Demographics with the example data as -

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

I want to get output as <AnnualSales>800000</AnnualSales>

and I am using this query:

select Demographics.query('/StoreSurvey/AnnualSales') 
    from Sales.Store

but I am getting error -

Msg 2260, Level 16, State 1, Line 1
XQuery [Sales.Store.Demographics.query()]: There is no element named 'StoreSurvey'

I can clearly see that there is a StoreSurvey element. Please let me know where i am going wrong

The error message is right. There is no element `StoreSurvey`. Read this: [MSDN: Handling Namespaces in XQuery](https://msdn.microsoft.com/en-us/library/ms187013.aspx) — Tomalak, Jul 19 '15 at 08:11
See http://stackoverflow.com/questions/5107790/syntax-for-xquery-with-namespace-in-the-node and http://stackoverflow.com/questions/25064078/how-to-get-specific-xml-namespace-in-xquery-in-sql-server — Tomalak, Jul 19 '15 at 08:16

score 3 · Accepted Answer · answered Jul 19 '15 at 09:01

3

You need to respect the XML namespaces in play!

<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">
             *********************************************************************************

Try this code:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey')
select 
    Demographics.value('(/StoreSurvey/AnnualSales)[1]', 'bigint') 
from 
    Sales.Store

answered Jul 19 '15 at 09:01

marc_s

732,580
175
1,330
1,459

When I try your method with another table of same type I get error - Msg 9314, Level 16, State 1, Line 2 XQuery [Production.ProductModel.Instructions.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type '{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions}:Location' within inferred type 'element({http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions}:Location,#anonymous) ?'. – sam Jul 19 '15 at 09:57
my query was with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions') select Instructions.value('(/root/Location)[1]','bigint') from Production.ProductModel where Instructions is not null – sam Jul 19 '15 at 09:57
@sam: sorry, but without **seeing** your XML and the code you use, I cannot possibly help you here - there's so many things that could be done wrong .... with your **shown XML**, my code will work - guaranteed. – marc_s Jul 19 '15 at 10:00
I have just created another question as I could not paste all of it in the comment window. the link to question is -http://stackoverflow.com/questions/31500056/xquery-value-method-not-working-and-is-giving-error – sam Jul 19 '15 at 10:06
@Sam: answered that question as well - please, if those answers help you solve your problem, then [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Jul 19 '15 at 10:43

Xquery method query() not working in SQL Server

1 Answers1