How to parse an XML embedded in a nvarchar column?

Below, you can find the text related to the question/problem. In the video, the question will be presented first, followed by the answers. If the video moves too fast, feel free to pause and review the answers. If you need more detailed information, you can find the necessary sources and links at the bottom of this description. I hope this video has been helpful, and even if it doesn't directly solve your problem, it will guide you to the source of the solution. I'd appreciate it if you like the video and subscribe to my channel!How to parse an XML embedded in a nvarchar column?

I have a problem which i'm currently working on and i can't wrap my head around it after reading the official documentation and seeing topics online, i'll try ot be as clear as possible:
I'm using Azure Synapse Analytics and i created a source which retrieves data from a SQL Server like so:

The mapping for my source to the sink is:

Now in the "request" column there's a string embeded in a XML format like so:

The problem that i'm trying to solve is that i need to create new columns for each node in the XML string, for example i need to create a new column named "MSP" with the value "U678202" taken from the "codiceMSP" node in the XML string, and so on.
So i created a new Data Flow and configured the source and the Parse transformation like so:

I created a source block taking data from the one copied in my azure storage and from then i created a Parse transformation block to parse the embedded XML data in a string and create the new columns from it. The problem is that the XML is not in a conventional format and i just can't make the parse work. I tried to set up the namespaces but the value of "codiceMSP" will not parse into the new MSP column. What am i doing wrong? How can i configure the XML Parse trasnformation to read through the nodes inside the request string? There's not so much information online about this specific case, any help will be really appreciated.
Thank you in advance for any help
Best regards

Tags: xml,azure,azure-data-factory,azure-synapse,azure-synapse-analyticsSource of the question:

Question and source license information: