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:
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:
<RESULTSET> <PROG>PROGX_</PROG> <STNTYPE>LEAKTEST</STNTYPE> <STNID>S1</STNID> ...etc... <OVERALL_STATUS>OK</OVERALL_STATUS> </RESULTSET>
(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.