Update badly formed XML to allow parsing I have inherited a production system that needs some fine tuning to enable easy extract of the result set.  A record gets added to a table which contains an XML field, with contents similar to the below:

&lt;PROG&gt;PROGX_&lt;/PROG&gt;&lt;STNTYPE&gt;LEAKTEST&lt;/STNTYPE&gt;&lt;STNID&gt;S1&lt;/STNID&gt;&lt;TRCODE&gt;SN_REQUEST&lt;/TRCODE&gt;&lt;OPERATORID&gt;222&lt;/OPERATORID&gt;&lt;PARTID&gt;PARTID&lt;/PARTID&gt;&lt;TRSTATUS&gt;0000&lt;/TRSTATUS&gt;&lt;TRTEXT&gt;TEST OK&lt;/TRTEXT&gt;&lt;T1_USL&gt;0.45&lt;/T1_USL&gt;&lt;T1_LSL&gt;-0.45&lt;/T1_LSL&gt;&lt;T1_RESULT&gt;05,CC/M&lt;/T1_RESULT&gt;&lt;T1_STATUS&gt;OK&lt;/T1_STATUS&gt;&lt;OVERALL_RESULT&gt;1&lt;/OVERALL_RESULT&gt;&lt;OVERALL_STATUS&gt;OK&lt;/OVERALL_STATUS&gt;


However, I believe the formatting of the XML is invalid and I want to be able to clean this up a bit, by adding a root and grouping this a bit better.  Something similar to along the lines of:

&lt;RESULTSET&gt;
    &lt;PROG&gt;PROGX_&lt;/PROG&gt;
    &lt;STNTYPE&gt;LEAKTEST&lt;/STNTYPE&gt;
    &lt;STNID&gt;S1&lt;/STNID&gt;
    ...etc...
    &lt;OVERALL_STATUS&gt;OK&lt;/OVERALL_STATUS&gt;
&lt;/RESULTSET&gt;


(The idea being then that I can use SQL views to then properly split this).

Any ideas if this is possible?  I'm new to XML on SQL.
You can get this information on SQL Server without adding a root element:

create table tbl (foo xml);

insert into tbl values ('&lt;PROG&gt;PROGX_&lt;/PROG&gt;&lt;STNTYPE&gt;LEAKTEST&lt;/STNTYPE&gt;&lt;STNID&gt;S1&lt;/STNID&gt;&lt;TRCODE&gt;SN_REQUEST&lt;/TRCODE&gt;&lt;OPERATORID&gt;222&lt;/OPERATORID&gt;&lt;PARTID&gt;PARTID&lt;/PARTID&gt;&lt;TRSTATUS&gt;0000&lt;/TRSTATUS&gt;&lt;TRTEXT&gt;TEST OK&lt;/TRTEXT&gt;&lt;T1_USL&gt;0.45&lt;/T1_USL&gt;&lt;T1_LSL&gt;-0.45&lt;/T1_LSL&gt;&lt;T1_RESULT&gt;05,CC/M&lt;/T1_RESULT&gt;&lt;T1_STATUS&gt;OK&lt;/T1_STATUS&gt;&lt;OVERALL_RESULT&gt;1&lt;/OVERALL_RESULT&gt;&lt;OVERALL_STATUS&gt;OK&lt;/OVERALL_STATUS&gt;
');




select 
    foo.value('(/PROG)[1]', 'nvarchar(100)') PROG,
    foo.value('(/STNTYPE)[1]', 'nvarchar(100)') STNTYPE,
    foo.value('(/STNID)[1]', 'nvarchar(100)') STNID
from   tbl;
GO




PROG   | STNTYPE  | STNID
:----- | :------- | :----
PROGX_ | LEAKTEST | S1   



db&lt;>fiddle here