What makes the cross apply query perform so poorly on this simple XML document, and perform exponentially slower as the dataset grows? It Is the use of the parent axis to get the attribute ID from the item node. It is this part of the query plan that is problematic. Notice the 423 rows coming … Read more
Actually you’re close to your goal, you just need to use nodes() method to split your rows and then get values: select s.SqmId, m.c.value(‘@id’, ‘varchar(max)’) as id, m.c.value(‘@type’, ‘varchar(max)’) as type, m.c.value(‘@unit’, ‘varchar(max)’) as unit, m.c.value(‘@sum’, ‘varchar(max)’) as [sum], m.c.value(‘@count’, ‘varchar(max)’) as [count], m.c.value(‘@minValue’, ‘varchar(max)’) as minValue, m.c.value(‘@maxValue’, ‘varchar(max)’) as maxValue, m.c.value(‘.’, ‘nvarchar(max)’) as Value, … Read more
SELECT b.BatchID, x.XmlCol.value(‘(ReportHeader/OrganizationReportReferenceIdentifier)[1]’,’VARCHAR(100)’) AS OrganizationReportReferenceIdentifier, x.XmlCol.value(‘(ReportHeader/OrganizationNumber)[1]’,’VARCHAR(100)’) AS OrganizationNumber FROM Batches b CROSS APPLY b.RawXml.nodes(‘/CasinoDisbursementReportXmlFile/CasinoDisbursementReport’) x(XmlCol); Demo: SQLFiddle