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.

2
  • What do you mean by "clean this up a bit"?
    – McNets
    Commented Dec 17, 2018 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
    Commented Dec 17, 2018 at 10:27

1 Answer 1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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