XPath query runs slow when referencing parent node using ../../<node name>

performance, sql, xpath

Solution

For the first part of avoiding ../../ you can use CROSS APPLY to to provide both data sets.

Also if you would like a faster INSERT and are not worried about logging, the SELECT INTO statement would provide a much faster insert:

SELECT
    s.value('(SessionID)[1]', 'int') SessionID,             
    r.value('(Heading)[1]', 'float') Heading,
    r.value('(Speed)[1]', 'float') Speed,
    r.value('(Height)[1]', 'float') Height,
    r.value('(FlowRate)[1]', 'float') FlowRate,
    r.value('(AppRate)[1]', 'float') AppRate,
    r.value('(Point/Lat)[1]', 'float') Latitude,
    r.value('(Point/Lng)[1]', 'float') Longtitude
INTO #Reading
FROM
    @XMLData.nodes('/Sessions') as S(s)
    CROSS APPLY 
    s.nodes('./Readings/Reading') XMLData(r)

Problem

I want to get all the Reading nodes and their values from each session in my xml document as well as obtaining the session ID from the parent node. I am running this on a SQL Server 2008 instance. My xml setup is: ``` <Sessions> <SessionID>99</SessionID> <Readings> <Reading> <Point> <Lat>-40.411558</Lat> <Lng>175.63504</Lng> </Point> <Heading>54</Heading> <Speed>0.1</Speed> <Height>0</Height> <FlowRate>0.69</FlowRate> <AppRate>74</AppRate> </Reading> ... more readings </Readings> <Sessions> ``` My original attempt (which works) was shown below however this seems extremely slow when I run it. However when I take out the line that add's the SessionID i.e ../../SessionID it seems to perform much faster. It takes a 12sec query down to just 2sec. ``` SELECT [SessionID] = c.value('(../../SessionID)[1]', 'int'), [Heading] = c.value('(Heading)[1]', 'float'), [Speed] = c.value('(Speed)[1]', 'float'), [Height] = c.value('(Height)[1]', 'float'), [FlowRate] = c.value('(FlowRate)[1]', 'float'), [AppRate] = c.value('(AppRate)[1]', 'float'), [Latitude] = c.value('(Point/Lat)[1]', 'float'), [Longtitude] = c.value('(Point/Lng)[1]', 'float') FROM @XMLData.nodes('/Sessions/Readings/Reading') XMLData(c) ``` Is there anyway I can do this without the need to reference SessionID using the ../../ notation. I do need the SessionID as this query is being used to insert records into a temporary table: ``` insert into #MyTempTable SELECT [SessionID] = c.value('(../../SessionID)[1]', 'int'), [Heading] = c.value('(Heading)[1]', 'float'), [Speed] = c.value('(Speed)[1]', 'float'), [Height] = c.value('(Height)[1]', 'float'), [FlowRate] = c.value('(FlowRate)[1]', 'float'), [AppRate] = c.value('(AppRate)[1]', 'float'), [Latitude] = c.value('(Point/Lat)[1]', 'float'), [Longtitude] = c.value('(Point/Lng)[1]', 'float') FROM @XMLData.nodes('/Sessions/Readings/Reading') XMLData(c) ```

Original source