<html><head>
<link media="all" type="text/css" href="/webmail/static/deg/css/wysiwyg-4230203003.css" rel="stylesheet">
</head><body>
Hi,<br><br>I am using xquery in a sql 2005 database. Many of the xquery functions and axis are not supported so I am trying to do some work-arounds. In the below example, I have a nested FLOWR $Y which searches the xml file for matches to the current context node "for $y in /foo:root/foo:data1[foo:part_number = $x/foo:part_number][1]"<br><br>This works, but is not efficient with large xml content. Sql server doesn't support xpath axises e.g. (parent::foo:root) unless it is used as a predicate, so I a limited on how to navigate up and down the document. Is there a better way? <br><br>DECLARE @x xml<br>SET @x='<foo:root xmlns:foo="http://www.foo/schema"><br> <foo:data1><br> <foo:part_number>1ASD4</foo:part_number><br> <foo:smr>smrcode</foo:smr><br> </foo:data1><br> <foo:data2><br> <foo:cage>09998</foo:cage><br> <foo:part_number>12234</foo:part_number><br> </foo:data2><br> <foo:data2><br> <foo:cage>04568</foo:cage><br> <foo:part_number>1ASD4</foo:part_number><br> </foo:data2><br> <foo:data2><br> <foo:cage>ASBB7</foo:cage><br> <foo:part_number>ZZZSSD4</foo:part_number><br> </foo:data2><br></foo:root>'<br>SELECT @x.query('declare namespace foo="http://www.foo/schema"; (<root> <br> {for $x in /foo:root/foo:data2 return <br> <doc><br> <pn>{$x/foo:part_number}</pn><br> <cage>{$x/foo:cage}</cage><br> <smr><br> {for $y in /foo:root/foo:data1[foo:part_number = $x/foo:part_number][1]<br> return<br> data($y/foo:smr)}<br> </smr><br> </doc>}<br></root>)')<br><br>Thanks,<br><br></body></html>