Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting XPath to FLWOR Expand / Collapse
Author
Message
Posted Monday, June 9, 2014 8:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. So far, I',m stumped.

Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
<Attribute Name="Ink..etc.." />
<Attribute Name="Bleed ..etc " />
<Drops returnCompany="ACME" returnAddress1="...etc...">
<Drop Number="1" Total="3141" Date="07/06/2014">
<Area Name="43001PBOX" FriendlyName="ALEXANDRIA, OH" Total="165" />
<Area Name="43001R001" FriendlyName="ALEXANDRIA, OH" Total="393" />
<Area Name="43001R002" FriendlyName="ALEXANDRIA, OH" Total="445" />
<Area Name="43023PBOX" FriendlyName="GRANVILLE, OH" Total="236" />
<Area Name="43023R001" FriendlyName="GRANVILLE, OH" Total="666" />
<Area Name="43023R002" FriendlyName="GRANVILLE, OH" Total="497" />
<Area Name="43031PBOX" FriendlyName="JOHNSTOWN, OH" Total="166" />
<Area Name="43031R001" FriendlyName="JOHNSTOWN, OH" Total="573" />
</Drop>
<Drop Number="2" Total="4279" Date="07/27/2014">
<Area Name="43031R002" FriendlyName="JOHNSTOWN, OH" Total="611" />
<Area Name="43031R003" FriendlyName="JOHNSTOWN, OH" Total="582" />
<Area Name="43031R004" FriendlyName="JOHNSTOWN, OH" Total="533" />
<Area Name="43031R005" FriendlyName="JOHNSTOWN, OH" Total="511" />
<Area Name="43055R003" FriendlyName="NEWARK, OH" Total="638" />
<Area Name="43062PBOX" FriendlyName="PATASKALA, OH" Total="361" />
<Area Name="43062R009" FriendlyName="PATASKALA, OH" Total="516" />
<Area Name="43062R017" FriendlyName="PATASKALA, OH" Total="527" />
</Drop>
</Drops>
</Product>
</order>
')

Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)

Results:
Name
43001PBOX
43001R001
43001R002
43023PBOX
43023R001
43023R002
43031PBOX
43031R001


Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data

Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001


Can anyone help me build a FLOWR-based query that produces the same result as the pure XPath one?
Post #1578862
Posted Tuesday, June 10, 2014 5:23 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853

Can anyone help me build a FLOWR-based query that produces the same result as the pure XPath one?


I don't know if that is possible. I thought I saw an article out there where someone replaces that nodes method (which you are using in your first query) with a FLOWR statement but I can't find it. I gave this a shot but failed

The best I could do for you is this solution:

WITH xml_txt(txt) AS
(
select cast(myXmlData.query
('
for $drop in /order/Product/Drops/Drop[@Number=1]/Area/@Name
return string($drop)
')
AS varchar(100))
from #data d
)
SELECT item
FROM xml_txt
CROSS APPLY dbo.DelimitedSplit8K(txt,' ') s

This uses Jeff Moden's splitter function (referenced in my signature). I tested this with 100,000 XML records in #data, however, and the FLOWR/Splitter solution runs for about 30 seconds whereas your original query runs for 8 seconds. Your query, when doing 1000's of records, produces a parallel query plan; my solution did not so I updated it to include Adam Mechanic's MakeParallel() function which reduced the query time by about 1/3rd but was still slower than the original solution that you posted.

The final solution looked like this:

WITH xml_txt(txt) AS
(
select cast(myXmlData.query
('
for $drop in /order/Product/Drops/Drop[@Number=1]/Area/@Name
return string($drop)
')
AS varchar(100))
from #data d
)
SELECT item
FROM make_parallel()
CROSS APPLY xml_txt
CROSS APPLY dbo.DelimitedSplit8K(txt,' ') s

All that said, you could clean your query up to look like this:

select data.mynodes.value('.', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('(/order/Product/Drops/Drop[@Number="1"]/Area/@Name)') data(mynodes);



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1579447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse