Nested FLOWRs

[Philip Vallone]

Thanks! 

________________________________

From: http://x-query.com/mailman/listinfo/talk [mailto:http://x-query.com/mailman/listinfo/talk] On Behalf
Of Michael Rys
Sent: Tuesday, November 04, 2008 4:40 PM
To: http://x-query.com/mailman/listinfo/talk; http://x-query.com/mailman/listinfo/talk
Subject: RE:  Nested FLOWRs



 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: http://x-query.com/mailman/listinfo/talk [mailto:http://x-query.com/mailman/listinfo/talk] On Behalf
Of http://x-query.com/mailman/listinfo/talk
Sent: Tuesday, November 04, 2008 4:09 AM
To: http://x-query.com/mailman/listinfo/talk
Subject:  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,