MICROSOFT @#$(*&@#$(*%&@#$... the saga continues...

One other thing... When you code:

Select (Select 1[Number] for XML Path('root'))

the output result type is character! I recoded, again, to the following:

Select Cast((Select 1[Number] for XML Path('root')) as XML)

and everything seems to work as expected with the Insert/Exec or just an Exec...