SQL Server - Parse/Read XML Data

In SQL Server - Episode 1, we explore how to read XML data from a database table into a SQL XML variable. Next, we perform a SELECT statement against the XML variable so we can output specific columns to the result pane. Finally, we learn how to fetch all the data columns from an XML variable.

[@egdteg7981]

Great job, clear and perfect pace ; thank you !

[@bstewart68]

This was exactly what I needed. Well done!

[@ScottLHolmes]

Hey, thanks for this. Excellent (and quick) explanation of the first steps. Just what I was looking for.

[@KarlsonAk]

Excellent! Thank you for the explanation!

[@user-hq5vd5ul3i]

Thanks, that was helpfull as a starting point!

[@mward89]

Super helpful, thank you!

[@higiniofuentes2551]

Wow! Thank you for this very useful video!

[@juliancolorado9232]

Excelent video

[@SQLUniversity]

Excellent coverage

[@FishingFamily_UA]

Thanks a lot! 🙌🙏

[@JonathanGoodchild]

Do you have a video about retrieving data from an XML column type and converting that into a temporary table? I can't seem to find anything on the web and I keep getting back to this answer which is the opposite of what I am looking for.

[@MaxLevelCode]

I don't have a video on this but I'll add to my list of videos to create.

[@ramyadonthi1381]

Excellent illustration, very helpful. I have slightly different requirement. Can you please provide solution for it! I have a sql table with one of the columns that's an xml type . I am trying to use xml nodes and value function but it's retuning all nulls!
I am using the below query where record_temp is the xml column name in table1
Select t1.*, a.t.value('(client_id[1]', varchar(50)') as client_uid from table1 t outer apply t.record_temp.nodes('//record_temp') as a(t)

[@MethodOverRide]

I have done something similar with FOR XML using a correlated sub queries but it didn't work well when we started writing out large XML files. I'm talking like 100k to a few million lines. We tried using c# script task in ssis and it didn't work out much better. Ended up using the query in a python script, breaking up the results into multiple files and wrote the results as a string then pretty printed the xml using more python.
Have you come across any native solutions in SSIS or SSMS that can write out large XML files, like 100mb to 800mb in size?

[@MaxLevelCode]

If the processing that you're doing doesn't have to be XML then I highly recommend you use JSON. XML has a significantly larger file size and has lots of overhead which causes it to generally be a lot slower. JSON is specifically designed for data interchange, which means the parser is a lot faster and less complicated. So when it comes to working with large files, you're going to have much better success if you work with JSON.
To answer your question, I have not worked with large (100 MBs+) XML files.

[@michamatracz706]

you can loop through this xml file then

[@sandeepverma-qi6lo]

how to identify columns in xml file