asked
Optimising XML to sql data parsing
Hello All, can anyone suggest better way for below problem please. **file attached.** as i am new to ask sql coould not able to format. **The Below logic is [link text][1] working fine for small set of data, but for data count 91087, even after 4 hrs query execution is still continue. can any one please suggest how to optimised it. or any alternative.** data DECLARE @X XML = ' Godrej chail xxx-712-xxxx 2009-09-30 tbzx Godrej Data 2009-09-30 FULL 2008-08-28 2008-06-10 2020-09-25 Light Duty Brakes 2 Professional Grade 816-4000 Light Duty Brakes 2 Professional Grade 816-4000 Standard Nut Requires Stamped Retainer And Cotter Pin 2 Professional Grade 816-4018
Thru 8/91 2 Professional Grade 816-4265 Manual 1 955-304 Reflector Packaging Type: Box 1 1650140 Composite Type Smoked Lens, w/Black Trim 1 1591142 Power w/Heat wo/Memory 1 955-1162 Compare to Original Bulb Call-Out Size: 194 Wattage: 1.2 Light Color: White 1 194W-SMD M14 x 1.50 Thread Package Quantity: 5 Dorman - AutoGrade - Boxed Packaging Type: Box 1 090-053 Wheel Lock Type: Female Spline Package Quantity: 4 Packaging Type: Card Thread Size: 1/2-20 Chrome Overall Length (In): 1.425 Steel Thread Handling: Right Hand Thread 1 711-221 1772522 ' -- solution ;With Cte AS (SELECT i.value('(../@action)[1]','varchar(20)') AppAction, i.value('(../@id)[1]','varchar(20)') AppId, i.value('.','varchar(20)') Notes, i.value('(..
/BaseVehicle/@id)[1]','varchar(20)') BaseVehicle, k.value('(.
/Qual/@id)[1]','varchar(20)') Qual, i.value('(../Qty)[1]','varchar(20)') Qty, i.value('(..
/PartType/@id)[1]','varchar(20)') PartType, i.value('(..
/SubModel/@id)[1]','varchar(20)') SubModel, i.value('(..
/EngineBase/@id)[1]','varchar(20)') EngineBase, i.value('(..
/EngineVIN/@id)[1]','varchar(20)') EngineVIN, k.value('(./MfrLabel)[1]','varchar(20)') MfrLabel, i.value('(..
/Position/@id)[1]','varchar(20)') PositionId, i.value('(../Part)[1]','varchar(20)') Part, k.value('(./Qual/param/@value)[1]','varchar(20)') ParamValue, j.value('.','varchar(20)') RecordCount FROM @X.nodes('AMUL/App[@action="A"]/Note') x(i) OUTER APPLY x.i.nodes('../../Footer/RecordCount')y(j) OUTER APPLY y.j.nodes('../../App')z(k)) SELECT AppAction,AppId,BaseVehicle,Qual,Qty,PartType,MfrLabel,PositionId,Part,ParamValue,SubModel,EngineBase,EngineVIN,RecordCount, STUFF((SELECT ' ; ' + Notes FROM Cte X WHERE X.BaseVehicle = Y.BaseVehicle GROUP BY BaseVehicle,Notes FOR XML PATH('')), 1, 2, '') Note FROM Cte Y GROUP BY BaseVehicle,AppAction,AppId,Qual,Qty,PartType,MfrLabel,PositionId,Part,ParamValue,SubModel,EngineBase,EngineVIN,RecordCount its working fine for small set of data, but for data count 91087, even after 4 hrs query execution is still continue. can any one please suggest how to optimised it. or any alternative. [1]: /storage/temp/3103-ask.txt
I think that's a lot of work to do in a CTE, especially when you then join back to the same CTE. I have 2 suggestions: 1. Change the CTE to a temp table with a clustered index on BaseVehicle. 2. Use a CROSS APPLY for the Note field in the final output. Here's the basic syntax for #2: SELECT x.*, n.Note FROM #xml x CROSS APPLY ( SELECT STUFF ( ( SELECT ' ; ' + Notes FROM #xml xn WHERE xn.BaseVehicle = x.BaseVehicle GROUP BY xn.BaseVehicle, xn.Notes FOR XML PATH('') ) , 1, 2, '' ) ) n (Note) ;
18 People are following this question.
