Converting XPath to FLWOR

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  June 9, 2014 at 8:49 am

  #307981

  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?

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  June 10, 2014 at 5:23 pm

  #1720606

  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 :laugh:

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

  "I cant 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."

  -- Itzik Ben-Gan 2001