Parse SQL XML column into dynamic temp tables

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  May 8, 2025 at 2:15 pm

  #4589070

  Hello SSC,

  I hope everyone is happy and healthy and ready for spring!

  I am looking for some alternatives to parsing an XML column in SQL. I have over 1000 rows in XML format that I would like to parse one time into a SQL table or multiple SQL tables. The caveat is that the file structure is different for each XML. I am looking to make this dynamic.

  As of now, we have a process that parses XML over and over again based on nodes that are passed in. I see the same files getting parsed over and over again based on different parameters. This takes forever. So, my vision is to simply parse the entire column one time, so I can process the data in a SQL table(s), rather than parsing the same file over and over again.

  I am not sure if a stored procedure would work for this, or perhaps some out of the box thinking with SSIS components or maybe even PowerShell.

  Any assistance would be greatly appreciated!

   

  The are no problems, only solutions. --John Lennon

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  May 8, 2025 at 2:42 pm

  #4589080

  Can you mock up two or three rows of source data, along with an idea of what the 'parsed' results will look like?

  You mention having XML stored in SQL Server, but then talk about parsing files – it's not clear what is going on. As for 'passing in nodes' – what does that mean?

  Getting some concrete examples written out will help clarify what is going on and what your requirement is.

  ---

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  May 8, 2025 at 6:20 pm

  #4589142

  Hi Phil,

  Thank you for responding as always. Long story short, I raised this issue with a senior developer on my team that understands the process. I will update this post at a later time.

  I hope all is well with you and family!

  Dave

  The are no problems, only solutions. --John Lennon