question

Peter001 avatar image
Peter001 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
sql-server-2008sql-server-2012xml
ask.txt (5.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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) ;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Tom.
0 Likes 0 ·
can any one suggest how we can automtically process the xml file put it into the table . can we do it through powershell using batch file, which user can excute to push data from xml to sql. please share your expertise, if it is possible. Thanks Techie.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.