question

kenb_SQL avatar image
kenb_SQL asked

Parsing XML Column in Table With Dynamic Number of Elements

Hi,

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 :

1663704719481.png

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:

1663704786950.png

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!

tsqlxmlparse
1663704719481.png (291.1 KiB)
1663704786950.png (171.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.