How to get SQL Server XQUERY to return something other than "There is no element named [Element]"

Question

Apologies if this is answered elsewhere. I keep getting the error message XQuery [Mytable.XMLData.nodes()]: There is no element named 'Answer'

SELECT 
ref.value('/','nvarchar(1000)')
FROM   Mytable CROSS APPLY xmldata.nodes('Answer') R(ref)

-

--XML of Row
<Answer xmlns="http://TempNameSpace.com/AnswerData.xsd" Type="Deliverable">
  <Deliverable>
    <Title>test</Title>
    <Description>test</Description>
    <DueDate>2010-02-16T08:59:59</DueDate>
  </Deliverable>
</Answer>

I've tried several different variations on getting the root node ('answer'), or any of the child nodes if, however i change my statement to read

SELECT 
ref.value('/','nvarchar(1000)')
FROM   Mytable CROSS APPLY xmldata.nodes('/') R(ref)

i get the result testtest2010-02-16T08:59:59

I'd ultimately like this data in tabular format, something like

SELECT 
    ref.value('/Title','nvarchar(1000)') as Title
    ref.value('/Description','nvarchar(1000)') as Description

etc..
    FROM   Mytable CROSS APPLY xmldata.nodes('/Deliverable') R(ref)

Thanks for your help

Might be of interest: http://stackoverflow.com/questions/1302064/sql-server-2005-xml-query-works-value-requires-singleton-found-xdtuntype — OMG Ponies, Mar 01 '10 at 21:55
yes that does help a bit. Seems when i remove the namespace declaration on the root node, i can now find my elements. However, none of my elements have explicit namespace prefixes(eg, blah ) Should it? i suppose i can just remove this xmlns="http://TempNameSpace.com/AnswerData.xsd" line from my xml, however i was planning on using that to validate the xml Thanks — Beta033, Mar 01 '10 at 22:05
OOPS!!! @Myself: Instead, pay attention to the (/)[1] part. that helps — Beta033, Mar 01 '10 at 22:08
Is there a better way of not having to declare the namespace in each reference? — Beta033, Mar 01 '10 at 22:13
@Beta033: yes!! See my answer - use `WITH XMLNAMESPACES(.....)` around your statements — marc_s, Mar 01 '10 at 22:13

score 5 · Accepted Answer · answered Mar 01 '10 at 22:10

You're not paying attention to the XML namespace in play:

<Answer xmlns="http://TempNameSpace.com/AnswerData.xsd" Type="Deliverable" 
        **********************************************

You need to take that into account when querying - do something like this:

;WITH XMLNAMESPACES('http://TempNameSpace.com/AnswerData.xsd' AS ns)
SELECT 
  ref.value('(ns:*)[1]', 'nvarchar(1000)')
FROM Mytable 
CROSS APPLY xmldata.nodes('/ns:Answer') R(ref)

You need to reference everything inside <Answer> with the ns: XML namespace prefix.

How to get SQL Server XQUERY to return something other than "There is no element named [Element]"

1 Answers1