XQuery On SQL Server - Performance Issues

Question

Working on XQuery in SQL Server. My understanding was that using CROSS/OUTER APPLY for the nodes would help improve performance, however the top query (below) performance SIGNIFICANTLY worse than the lower. Can anyone help explain why that is, and perhaps any pointers on XQuery performance? I've searched all of the questions I can find but nothing seems directly on point.

Poor Performer

SELECT x.ApplicationId
    , t.value('(reportId/text())[1]','varchar(100)') AS ReportId
    , t.value('(reportType/text())[1]','varchar(100)') AS ReportType
    , t.value('(tracking-number/text())[1]','varchar(50)') AS TrackingNumber
    , n.value('(firstName/text())[1]','varchar(100)') AS FirstName
    , n.value('(middleName/text())[1]','varchar(100)') AS MiddleInitial
    , n.value('(lastName/text())[1]','varchar(100)') AS LastName
    , ssn.value('(ssn/text())[1]','varchar(50)') AS SSN
    , dob.value('(dob/text())[1]','varchar(30)') AS DateOfBirth
FROM #xml x
    CROSS APPLY x.xmlResponse.nodes('/xml-response') t1(t)
    OUTER APPLY t1.t.nodes('personPii/applicantInformation') t2(ai)
    OUTER APPLY t2.ai.nodes('name') t3(n)
    OUTER APPLY t2.ai.nodes('ssn') t4(ssn)
    OUTER APPLY t2.ai.nodes('dob') t5(dob)

Better Performer -why?

SELECT x.ApplicationId
    , x.XMLResponse.value('(/xml-response/reportId)[1]','varchar(100)') AS ReportId
    , x.XMLResponse.value('(/xml-response/reportType)[1]','varchar(100)') AS ReportType
    , x.XMLResponse.value('(/xml-response/tracking-number)[1]','varchar(50)') AS TrackingNumber
    , x.XMLResponse.value('(/xml-response/personPii/applicantInformation/firstName)[1]','varchar(100)') AS FirstName
    , x.XMLResponse.value('(/xml-response/personPii/applicantInformation/middleName)[1]','varchar(100)') AS MiddleInitial
    , x.XMLResponse.value('(/xml-response/personPii/applicantInformation/lastName)[1]','varchar(100)') AS LastName
    , x.XMLResponse.value('(/xml-response/personPii/applicantInformation/ssn/ssn)[1]','varchar(50)') AS SSN
    , x.XMLResponse.value('(/xml-response/personPii/applicantInformation/dob/dob)[1]','varchar(30)') AS DateOfBirth
FROM #xml x

Answer 1

Adding an example of nodes vs simple xml:

create table #data (xml xml)
insert into #data
select '<root><data><field>X</field><field>Y</field></data></root>'

select xml.value('root[1]/data[1]/field[1]', 'nvarchar(max)')
from #data d

select n.value('(text())[1]', 'nvarchar(max)')
from #data d
cross apply xml.nodes('root/data/field') t(n)

Here, you can see the difference how nodes work. The first version cannot simply fetch more than one field value

Answer 2

Here is another example for you.

Notable points:

• XML is inherently hierarchical, so the CROSS APPLY simulates one-to-many relationship between states and their cities.
• It is always better to use .../text() for the untyped XML elements for performance reasons.

SQL

DECLARE @xml XML =
N'<root>
   <state>
      <StateName>Florida</StateName>
      <Abbr>FL</Abbr>
      <Capital>Tallahassee</Capital>
      <cities>
         <city>
            <city>Miami</city>
            <population>470194</population>
         </city>
         <city>
            <city>Orlando</city>
            <population>285713</population>
         </city>
      </cities>
   </state>
   <state>
      <StateName>Texas</StateName>
      <Abbr>TX</Abbr>
      <Capital>Austin</Capital>
      <cities>
         <city>
            <city>Houston</city>
            <population>2100263</population>
         </city>
         <city>
            <city>Dallas</city>
            <population>5560892</population>
         </city>
      </cities>
   </state>
</root>';

SELECT state.value('(StateName/text())[1]', 'VARCHAR(20)') AS State
    , state.value('(Abbr/text())[1]', 'VARCHAR(20)') AS Abbr
    , state.value('(Capital/text())[1]', 'VARCHAR(20)') AS Capital
    , city.value('(city/text())[1]', 'VARCHAR(20)') AS city
    , city.value('(population/text())[1]', 'INT') AS population
FROM @xml.nodes('/root/state') AS t1(state)
    CROSS APPLY state.nodes('cities/city') AS t2(city);

Output

State	Abbr	Capital	city	population
Florida	FL	Tallahassee	Miami	470194
Florida	FL	Tallahassee	Orlando	285713
Texas	TX	Austin	Houston	2100263
Texas	TX	Austin	Dallas	5560892