Please don't post duplicate posts on the same topic.  You already have a thread on this topic.

There are three issues with your query.

1)  Your XML uses a namespace, and you have not adjusted your query to account for that.  The easiest way to do...

You have been pointed to an article that tells you the correct way to post data multiple times. (it's the first link in my signature and also Lynn's signature.)  You...

Most people are hesitant to open Excel files posted by random strangers on a public site.  The first link in my signature covers the proper way to post data.  Please...

It's not a simple pivot.  It's a combination of a pivot and an unpivot.  And, because the number of rows isn't constant, it will need to be dynamic.

I...

Have you tried using TRY_CAST( ColXML AS XML) to determine which value(s) cannot be converted to XML, and then seeing if there are any obvious characters in those values that could...