1

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:

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

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.

  • What do you mean by "clean this up a bit"? – McNets Dec 17 '18 at 10:24
  • Improve the structure, add the grouping node etc. I then want to be able to correctly parse this using a view into the child components as I then need to do some analysis on some of the results being captured. – AdzzzUK Dec 17 '18 at 10:27
1

You can get this information on SQL Server without adding a root element:

create table tbl (foo xml);

insert into tbl values ('<PROG>PROGX_</PROG><STNTYPE>LEAKTEST</STNTYPE><STNID>S1</STNID><TRCODE>SN_REQUEST</TRCODE><OPERATORID>222</OPERATORID><PARTID>PARTID</PARTID><TRSTATUS>0000</TRSTATUS><TRTEXT>TEST OK</TRTEXT><T1_USL>0.45</T1_USL><T1_LSL>-0.45</T1_LSL><T1_RESULT>05,CC/M</T1_RESULT><T1_STATUS>OK</T1_STATUS><OVERALL_RESULT>1</OVERALL_RESULT><OVERALL_STATUS>OK</OVERALL_STATUS>
');
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<>fiddle here

  • Thanks for this approach. I figured there was an alternative method to get what I needed, and this does it. Much appreciated. – AdzzzUK Dec 17 '18 at 13:16
  • I'm glad to help. – McNets Dec 17 '18 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.