How to Enable XPath ancestor Function in SQL Server 2008

Question

I have one SQL Server running 2008 R2 (RTM) 10.50.1617.0 (X64), and I'm leasing a server using 2008 (SP3) - 10.0.5500.0 (Intel X86) Standard Edition. On the R2 (RTM) box I'm able to use the XPath ancestor:: function, but on the SP3 machine I get the error "The XQuery syntax 'ancestor' is not supported." I've been searching the web to see if I can figure out what the system requirements are for SQL Server to have the ancestor:: function available, but haven't turned up anything definitive yet. Being able to use the ancestor:: function right in my T-SQL is a real boon as it saves me from a lengthy workaround; does anyone know what component I need to have installed with Standard Edition to get this functionality (if it's possible at all)?

Thanks in advance!

Answer 1

I didn't think ancestor was supported at all.  Are you sure that codepath is firing on your R2 box?  I also just checked in SQL 2012 RTM and it doesn't appear to be supported.

Can you post a simple example of what you're trying to do?  Maybe there is a more straightforward workaround, eg

DECLARE @xml XML = '<?xml version="1.0" encoding="ISO-8859-1"?>
 <bookstore>

 <book>
   <title lang="eng">Harry Potter</title>
   <price>29.99</price>
 </book>

 <book>
   <title lang="eng">Learning XML</title>
   <price>39.95</price>
 </book>

 </bookstore>'


-- Return book ancestor element for title "Harry Potter" with valid XQuery ( using QueryMachine.XQuery )
-- (: Return book ancestor element for title "Harry Potter" :)
-- doc("c:\temp\temp.xml")//book[title[.="Harry Potter"]/ancestor::node()]

/* Result
<book>
  <title lang="eng">Harry Potter</title>
  <price>29.99</price>
</book>
*/


-- Return book ancestor element for title "Harry Potter" using SQL Server supported XQuery
SELECT @xml.query('/bookstore/book[title="Harry Potter"]')

Answer 2

Thanks for asking, Rob--your question made me take another look at the code and I realized where I got myself confused. I used OPENXML on our RTM machine, but was trying to just use the nodes function on the Standard one. I tried out OPENXML on the Standard one and it works like a charm. (And, yes, the nested "parent" nodes are a pretty annoying XML structure--it was an unfortunate decision by a third-party developer and the reason we have need the ancestor function.)

DECLARE @x xml
DECLARE @h int

SET @x = CAST('<content>
<parent id="1" name="grandparent">
<parent id="1.1" name="parent">
<child id="a" />
</parent>
</parent>
</content>' AS xml)

EXEC sp_xml_preparedocument @h OUTPUT, @x

SELECT childID, grandparentID
FROM OPENXML(@h, '//child', 2)
WITH (childID varchar(5) '@id'
, grandparentID varchar(5) 'ancestor::parent[@name="grandparent"]/@id')

EXEC sp_xml_removedocument @h

Answer 3

You can achieve that with nodes, using (I think) the same information, eg

DECLARE @xml XML = '<content>
  <parent id="1" name="grandparent">
    <parent id="1.1" name="parent">
      <child id="a" />
    </parent>
  </parent>
</content>'

SELECT
	a.b.value('(child/@id)[1]', 'VARCHAR(5)') childId,
	x.y.value('@id', 'VARCHAR(5)') grandparentId
FROM @xml.nodes('//parent[@name="grandparent"]') x(y)
	CROSS APPLY x.y.nodes('*') a(b)

Don't know if that does what you want?

Answer 4

For my example code it would do the same thing, but in the real-world example I have to find any ancestor that might be at any level (the 'parent' nodes go several nested nodes deep), and the important thing is whether any ancestor of the child has that particular attribute. Sorry I can't go into any more detail, but the developer considers their XML model proprietary. I've used several different approaches, but the ancestor function is definitely the simplest. Thanks again!