I'm trying to parse the various elements of an XML column in my SQL table. This is an example of what the XML structure can look like :
The issue is the number of elements can vary between each record. For example, one record might not have a MANAGEREMAIL element because one has not been assigned yet or another example could be the EMPLOYEEEMAIL is missing, so it wouldn't show up either. The other issue is that I'm having a hard time grabbing the scriptname value of the element. Ideally, I would like the output to be something like you see in the above image at the bottom.
So far, this is what I've come up with:
The above SQL works as long as there are the same number of elements in the XML field, for each record, however as I stated earlier, the number of elements in the XML can change.
Thanks for your help!