[xquery-talk] Nested FLOWRs

[Michael Rys]

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

Note that SQL Server support the parent axis, but not the optional axis feature. So you can write:

SELECT @x.query('declare namespace foo="http://www.foo/schema"; (<root>
    {for $x in /foo:root/foo:data2 return
    <doc>
        <pn>{$x/foo:part_number}</pn>
        <cage>{$x/foo:cage}</cage>
        <smr>
            {data(($x/../foo:data1[foo:part_number = $x/foo:part_number]/foo:smr)[1])}
        </smr>
    </doc>}
</root>)')

However, the parent axis is not as efficient as forward navigation, so rewriting this into a forward only expression should be faster:

SELECT @x.query('declare namespace foo="http://www.foo/schema"; (<root>
    {for $y in /foo:root, $x in $y/foo:data2 return
    <doc>
        <pn>{$x/foo:part_number}</pn>
        <cage>{$x/foo:cage}</cage>
        <smr>
            {data(($y/foo:data1[foo:part_number = $x/foo:part_number]/foo:smr)[1])}
        </smr>
    </doc>}
</root>)')

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.

Best regards
Michael



From: talk-bounces at x-query.com [mailto:talk-bounces at x-query.com] On Behalf Of philip.vallone at verizon.net
Sent: Tuesday, November 04, 2008 4:09 AM
To: talk at x-query.com
Subject: [xquery-talk] Nested FLOWRs

Hi,

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

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?

DECLARE @x xml
SET @x='<foo:root xmlns:foo="http://www.foo/schema">
    <foo:data1>
        <foo:part_number>1ASD4</foo:part_number>
        <foo:smr>smrcode</foo:smr>
    </foo:data1>
    <foo:data2>
        <foo:cage>09998</foo:cage>
        <foo:part_number>12234</foo:part_number>
    </foo:data2>
    <foo:data2>
        <foo:cage>04568</foo:cage>
        <foo:part_number>1ASD4</foo:part_number>
    </foo:data2>
    <foo:data2>
        <foo:cage>ASBB7</foo:cage>
        <foo:part_number>ZZZSSD4</foo:part_number>
    </foo:data2>
</foo:root>'
SELECT @x.query('declare namespace foo="http://www.foo/schema"; (<root>
    {for $x in /foo:root/foo:data2 return
    <doc>
        <pn>{$x/foo:part_number}</pn>
        <cage>{$x/foo:cage}</cage>
        <smr>
            {for $y in /foo:root/foo:data1[foo:part_number = $x/foo:part_number][1]
            return
            data($y/foo:smr)}
        </smr>
    </doc>}
</root>)')

Thanks,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://x-query.com/pipermail/talk/attachments/20081104/7adfb3ce/attachment.htm