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.
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)
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?
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.
Great job, clear and perfect pace ; thank you !
Hey, thanks for this. Excellent (and quick) explanation of the first steps. Just what I was looking for.
This was exactly what I needed. Well done!
Wow! Thank you for this very useful video!
Excellent! Thank you for the explanation!
Super helpful, thank you!
Thanks, that was helpfull as a starting point!
Excellent coverage
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.
I don't have a video on this but I'll add to my list of videos to create.
Excelent video
0:42
4:49 - Now Let us just test this
7:03
Thanks a lot! 🙌🙏
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)
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?
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.
how to identify columns in xml file
you can loop through this xml file then