Performance Issue with cross apply while reading XML nodes for large dataset

Question

Performance issue with XML cross apply:

DataTable has 1300 entries and the field xmldata has 250 nodes, so the query is running 1300 * 250 times to brings the output and the execution times takes a while.. about an hour to generate 325000 rows. Does anybody face a similar issue with the large dataset? Your help is highly appreciated.

Sample XML:

<dataModel>
  <Colum1>
    <value />
    <displayText />
    <controltype>textbox</controltype>
    <label>Field1</label>
    <controlid>4458575-b0d3-ff4d-01ac-5447e21234dd</controlid>
  </Colum1>
  <Colum2>
    <value />
    <displayText />
    <controltype>textbox</controltype>
    <label>Field2</label>
    <controlid>5a5b7b7e-7b66-1f0d-a562-9d0660a74e11</controlid>
  </Colum2>
....
</dataModel>

select  t.c.value('(local-name(.))[1]', 'nvarchar(100)') as keyname ,
        t.c.value('(controlid)[1]', 'nvarchar(200)') as controlid,
        t.c.value('(label)[1]', 'nvarchar(500)') as label
from DataTable xmldata 
CROSS APPLY xmldata .nodes('/dataModel/*') T(c)

Thanks

Answer 1

Your approach seems to be pretty straight forward. Not much space for enhancements...

The following is just a tiny change, but might speed up things:

declare @tbl TABLE(ID INT IDENTITY, xmldata XML);
INSERT INTO @tbl VALUES
(N'<dataModel>
  <Colum1>
    <value />
    <displayText />
    <controltype>textbox</controltype>
    <label>Field1</label>
    <controlid>4458575-b0d3-ff4d-01ac-5447e21234dd</controlid>
  </Colum1>
  <Colum2>
    <value />
    <displayText />
    <controltype>textbox</controltype>
    <label>Field2</label>
    <controlid>5a5b7b7e-7b66-1f0d-a562-9d0660a74e11</controlid>
  </Colum2>
</dataModel>');

select  t.c.value('(local-name(.))[1]', 'nvarchar(100)') as keyname ,
        t.c.value('(controlid/text())[1]', 'nvarchar(200)') as controlid,
        t.c.value('(label/text())[1]', 'nvarchar(500)') as label
from @tbl xmldata 
CROSS APPLY xmldata .nodes('/dataModel/*') T(c);

I added /text() to your XPaths (find details here).

It would be kind to tell us, how much difference you've encountered using /text(), thx.

And important to know: One very expensive part with XML is the initial parsing. Make sure, that the table's column is natively xml typed and that your run-time measurement is not biased by any loading / reading / parsing action (find details here).