title: cross apply xml query performs exponentially worse as xml document grows
body: <h2>What I Have</h2>
  
  <p>I have a variable size XML document that needs to be parsed on MSSQL 2008 R2 that looks like this:</p>
  
  <pre><code>&lt;data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False"&gt;
    &lt;item name="1"&gt;
      &lt;field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2"&gt;0.506543009706267&lt;/field&gt;
      &lt;field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2"&gt;-0.79500402346138&lt;/field&gt;
      &lt;field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2"&gt;0.0152649050024924&lt;/field&gt;
    &lt;/item&gt;
    &lt;item name="2"&gt;
      &lt;field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2"&gt;0.366096802804087&lt;/field&gt;
      &lt;field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2"&gt;-0.386642801354842&lt;/field&gt;
      &lt;field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2"&gt;0.031671174184115&lt;/field&gt;
    &lt;/item&gt;
  &lt;/data&gt;
  </code></pre>
  
  <p>.</p>
  
  <h2>What I Want</h2>
  
  <p>I need to transform it into a regular table type dataset that looks like this:</p>
  
  <pre><code>item_name field_id                             field_type  field_value
  --------- ------------------------------------ ----------- ---------------
  1         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.5065430097062
  1         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.795004023461
  1         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.0152649050024
  2         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.3660968028040
  2         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.386642801354
  2         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.0316711741841
  3         EA032B25-19F1-4C1B-BDDE-3113542D13A5 2           0.8839620369590
  3         71014ACB-571B-4C72-9C9B-05458B11335F 2           -0.781459993268
  3         740C36E9-1988-413E-A1D5-B3E5B4405B45 2           0.2284423515729
  </code></pre>
  
  <p>.</p>
  
  <h2>What Works</h2>
  
  <p>This <code>cross apply</code> query creates the desired output:</p>
  
  <pre><code>create table #temp (x xml)
  
  insert into #temp (x)
  values ('
  &lt;data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False"&gt;
    &lt;item name="1"&gt;
      &lt;field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2"&gt;0.506543009706267&lt;/field&gt;
      &lt;field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2"&gt;-0.79500402346138&lt;/field&gt;
      &lt;field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2"&gt;0.0152649050024924&lt;/field&gt;
    &lt;/item&gt;
    &lt;item name="2"&gt;
      &lt;field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2"&gt;0.366096802804087&lt;/field&gt;
      &lt;field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2"&gt;-0.386642801354842&lt;/field&gt;
      &lt;field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2"&gt;0.031671174184115&lt;/field&gt;
    &lt;/item&gt;
    &lt;item name="3"&gt;
      &lt;field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2"&gt;0.883962036959074&lt;/field&gt;
      &lt;field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2"&gt;-0.781459993268713&lt;/field&gt;
      &lt;field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2"&gt;0.228442351572923&lt;/field&gt;
    &lt;/item&gt;
  &lt;/data&gt;
  ')
  
  select c.value('(../@name)','varchar(5)') as item_name
        ,c.value('(@id)','uniqueidentifier') as field_id
        ,c.value('(@type)','int') as field_type
        ,c.value('(.)','nvarchar(15)') as field_value
  from   #temp cross apply
         #temp.x.nodes('/data/item/field') as y(c)
  
  drop table #temp
  </code></pre>
  
  <p>.</p>
  
  <h2>Problem</h2>
  
  <p>When there are a few hundred (or fewer) <code>&lt;item&gt;</code> elements in the XML, the query performs just fine.  However, when there are 1,000 <code>&lt;item&gt;</code> elements, it takes 24 seconds to finish returning the rows in SSMS.  When there are 6,500 <code>&lt;item&gt;</code> elements, it takes about 20 minutes to run the <code>cross apply</code> query.  We could have 10-20,000 <code>&lt;item&gt;</code> elements.</p>
  
  <p>.</p>
  
  <h2>Questions</h2>
  
  <p>What makes the <code>cross apply</code> query perform so poorly on this simple XML document, and perform exponentially slower as the dataset grows?</p>
  
  <p>Is there a more efficient way to transform the XML document into the tabular dataset (in SQL)?</p>