<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:D="DAV:" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<link rel=Stylesheet type="text/css" media=all
href="/webmail/static/deg/css/wysiwyg-4230203003.css">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> Hi Philip… it is not clear to me why you need the parent axis to
do your query or what exactly your required output is (sometimes you copy the
value and sometimes you copy the full element).<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Note that SQL Server support the parent axis, but not the
optional axis feature. So you can write:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:blue'>SELECT</span><span style='font-size:
10.0pt;font-family:"Lucida Console"'> @x<span style='color:gray'>.</span>query<span
style='color:gray'>(</span><span style='color:red'>'declare namespace
foo="http://www.foo/schema"; (<root> <o:p></o:p></span></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> {for $x in /foo:root/foo:data2
return <o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> <doc><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'>
<pn>{$x/foo:part_number}</pn><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'>
<cage>{$x/foo:cage}</cage><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> <smr><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> {data(($x/../foo:data1[foo:part_number
= $x/foo:part_number]/foo:smr)[1])}<o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> </smr><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> </doc>}<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Console";
color:red'></root>)'</span><span style='font-size:10.0pt;font-family:
"Lucida Console";color:gray'>)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:14.0pt;font-family:"Lucida Console";
color:gray'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>However, the parent axis is not as efficient as forward
navigation, so rewriting this into a forward only expression should be faster:<o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:14.0pt;
font-family:"Lucida Console";color:blue'><o:p> </o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:blue'>SELECT</span><span style='font-size:
10.0pt;font-family:"Lucida Console"'> @x<span style='color:gray'>.</span>query<span
style='color:gray'>(</span><span style='color:red'>'declare namespace
foo="http://www.foo/schema"; (<root> <o:p></o:p></span></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> {for $y in /foo:root, $x in
$y/foo:data2 return <o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> <doc><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'>
<pn>{$x/foo:part_number}</pn><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'>
<cage>{$x/foo:cage}</cage><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> <smr><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'>
{data(($y/foo:data1[foo:part_number = $x/foo:part_number]/foo:smr)[1])}<o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> </smr><o:p></o:p></span></p>
<p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;
font-family:"Lucida Console";color:red'> </doc>}<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Lucida Console";
color:red'></root>)'</span><span style='font-size:10.0pt;font-family:
"Lucida Console";color:gray'>)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Finally, note that the XQuery construction is not that well performing
for large amounts of data as if you were using FOR XML. Contact me if you need
help with that, since that is not XQuery specific.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Best regards<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Michael<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
talk-bounces@x-query.com [mailto:talk-bounces@x-query.com] <b>On Behalf Of </b>philip.vallone@verizon.net<br>
<b>Sent:</b> Tuesday, November 04, 2008 4:09 AM<br>
<b>To:</b> talk@x-query.com<br>
<b>Subject:</b> [xquery-talk] Nested FLOWRs<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-bottom:12.0pt'>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,<o:p></o:p></p>
</div>
</body>
</html>