Reading unstructured XML data

[sasireka]

<?xml version="1.0" encoding="UTF-8"?><message>
<software-version>4.1.1810-65</software-version>
<source>pia</source>
<spec-version>6</spec-version>
<message-name>roll-manifest-data</message-name>

<roll-summary>
<output-roll-id>A00812135</output-roll-id>
<input-rolls/>
<group-jobs/>
<jobs>
<job type="diagnostic" distance-run="1583" units="inches" id="spit-page-lead-in-sfhc-hi_0xqo6"/>
<job type="single" distance-run="32635" units="inches">
<job-submission-id>712537-15788-KPIC_PG-116-KPIC-00768.s1</job-submission-id>
<frames>
<printed-ok>29400</printed-ok>
<printed-error>0</printed-error>
</frames>
</job>
<job type="single" distance-run="326354" units="inches">
<job-submission-id>712537-15788-KPIC_PG-116-KPIC-0094.s1</job-submission-id>
<frames>
<printed-ok>29500</printed-ok>
<printed-error>0</printed-error>
</frames>
</job>
</jobs>
</roll-summary>
<jobs>
<job>
<customer-job-id>spit-page-lead-in-sfhc-hi</customer-job-id>
<job-submission-id>spit-page-lead-in-sfhc-hi_0xqo6</job-submission-id>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame00</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>2155475</serial-number>
<printed-timestamp>2021-03-23T07:13:44.091</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>2</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-b</side>
<serial-number>215548</serial-number>
<printed-timestamp>2021-03-23T07:14:09.460-0700</printed-timestamp>
</side-b>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>7</copy-relative-frame-number>
<copy-relative-signature-number>4</copy-relative-signature-number>
<side>side-a</side>
<serial-number>215553</serial-number>
<printed-timestamp>2021-03-23T07:13:44.819</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy-frame</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>8</copy-relative-frame-number>
<copy-relative-signature-number>4</copy-relative-signature-number>
<side>side-b</side>
<serial-number>215554</serial-number>
<printed-timestamp>2021-03-23T07:14:09.888</printed-timestamp>
</side-b>
</end-range>
<start-printing>
<sequence>2</sequence>
<timestamp>2021-03-23T07:13:44.228</timestamp>
</start-printing>
<start-printing>
<sequence>3</sequence>
<timestamp>2021-03-23T07:13:58.152</timestamp>
</start-printing>
<end-printing>
<sequence>4</sequence>
<timestamp>2021-03-23T07:14:09.890</timestamp>
<position units="inches">1549.27834</position>
</end-printing>
</job-manifest>
<content-metadata/>
</job>

</jobs>
</message>

Above i have given the sample data. Here i have to get the data from <jobs>.
Expected output is

customer-job-id	serialnumber(start-range -> side-a)	serialnumber(start-range -> side-b)	serialnumber(end-range -> side-a)	serialnumber(end-range -> side-a)

I have three scenarios:
1)Inside <jobs> some <job> will have all the data correctly (ex : first job). In that case i need the output as below

customer-job-id	serialnumber(start-range -> side-a)	serialnumber(start-range -> side-b)	serialnumber(end-range -> side-a)	serialnumber(end-range -> side-a)
712537-15789-KPIC_PG-120-KPIC-001	215547	215548	215553	215554

[ITWhisperer]

Assuming you have a typo in the last number of the third case of expected output (should be 297422 from the xml)

| makeresults | eval _raw="<?xml version=\"1.0\" encoding=\"UTF-8\"?><message>
<software-version>4.1.1810-65</software-version>
<source>pia</source>
<spec-version>6</spec-version>
<message-name>roll-manifest-data</message-name>

<roll-summary>
<output-roll-id>A0081215</output-roll-id>
<input-rolls/>
<group-jobs/>
<jobs>
<job type=\"diagnostic\" distance-run=\"1583\" units=\"inches\" id=\"spit-page-lead-in-sfhc-hi_0xqo6\"/>
<job type=\"single\" distance-run=\"32635\" units=\"inches\">
<job-submission-id>712537-15788-KPIC_PG-116-KPIC-008.s1</job-submission-id>
<frames>
<printed-ok>2900</printed-ok>
<printed-error>0</printed-error>
</frames>
</job>
<job type=\"single\" distance-run=\"32635\" units=\"inches\">
<job-submission-id>712537-15788-KPIC_PG-116-KPIC-009.s1</job-submission-id>
<frames>
<printed-ok>2900</printed-ok>
<printed-error>0</printed-error>
</frames>
</job>
</jobs>
</roll-summary>
<jobs>
<job>
<customer-job-id>spit-page-lead-in-sfhc-hi</customer-job-id>
<job-submission-id>spit-page-lead-in-sfhc-hi_0xqo6</job-submission-id>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame000001</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>215547</serial-number>
<printed-timestamp>2021-03-23T07:13:44.091-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame000002</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>2</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-b</side>
<serial-number>215548</serial-number>
<printed-timestamp>2021-03-23T07:14:09.460-0700</printed-timestamp>
</side-b>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame000007</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>7</copy-relative-frame-number>
<copy-relative-signature-number>4</copy-relative-signature-number>
<side>side-a</side>
<serial-number>215553</serial-number>
<printed-timestamp>2021-03-23T07:13:44.819-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>spit-page-lead-in-sfhc-hi_0xqo6-copy001-frame000008</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>8</copy-relative-frame-number>
<copy-relative-signature-number>4</copy-relative-signature-number>
<side>side-b</side>
<serial-number>215554</serial-number>
<printed-timestamp>2021-03-23T07:14:09.888-0700</printed-timestamp>
</side-b>
</end-range>
<start-printing>
<sequence>2</sequence>
<timestamp>2021-03-23T07:13:44.228-0700</timestamp>
</start-printing>
<start-printing>
<sequence>3</sequence>
<timestamp>2021-03-23T07:13:58.152-0700</timestamp>
</start-printing>
<end-printing>
<sequence>4</sequence>
<timestamp>2021-03-23T07:14:09.890-0700</timestamp>
<position units=\"inches\">1549.2783333333334</position>
</end-printing>
</job-manifest>
<content-metadata/>
</job>
<job>
<customer-job-id>712537-15789-KPIC_PG-120-KPIC-001</customer-job-id>
<job-submission-id>712537-15789-KPIC_PG-120-KPIC-001.s1</job-submission-id>
<external-job-id>712537-15789-KPIC_PG-120-KPIC-001_1616510859121</external-job-id>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<universal-frame-id>712537-15789-KPIC_PG-120-KPIC-001.s1-copy001-frame000001</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>223735</serial-number>
<printed-timestamp>2021-03-23T07:50:29.820-0700</printed-timestamp>
</side-a>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<universal-frame-id>712537-15789-KPIC_PG-120-KPIC-001.s1-copy001-frame000331</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>331</copy-relative-frame-number>
<copy-relative-signature-number>166</copy-relative-signature-number>
<side>side-a</side>
<serial-number>224065</serial-number>
<printed-timestamp>2021-03-23T07:50:45.828-0700</printed-timestamp>
</side-a>
</end-range>
<start-printing>
<sequence>2</sequence>
<timestamp>2021-03-23T07:50:29.847-0700</timestamp>
</start-printing>
<start-printing>
<sequence>3</sequence>
<timestamp>2021-03-23T07:50:42.545-0700</timestamp>
</start-printing>
<end-printing>
<sequence>4</sequence>
<timestamp>2021-03-23T07:51:00.901-0700</timestamp>
<position units=\"inches\">52302.92333333333</position>
</end-printing>
</job-manifest>
<content-metadata/>
</job>
<job>
<customer-job-id>18\'\'_DIVIDER_SS_Books_TOP</customer-job-id>
<job-submission-id>18\'\'_DIVIDER_SS_Books_TOP.s2224</job-submission-id>
<external-job-id>18\'\'_DIVIDER_SS_Books_TOP_1616513629960</external-job-id>
<type>customer</type>
<status>keep</status>
<sequence>32</sequence>
<printed-on-roll>complete</printed-on-roll>
<job-manifest>
<start-range>
<sequence>0</sequence>
<side-a>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000001</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>297399</serial-number>
<printed-timestamp>2021-03-23T09:29:37.992-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000002</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>2</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-b</side>
<serial-number>297400</serial-number>
<printed-timestamp>2021-03-23T09:30:03.315-0700</printed-timestamp>
</side-b>
<side-a>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000001</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>297399</serial-number>
<printed-timestamp>2021-03-23T09:29:37.992-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000002</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>2</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-b</side>
<serial-number>297400</serial-number>
<printed-timestamp>2021-03-23T09:30:03.315-0700</printed-timestamp>
</side-b>

<side-a>

<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000001</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>1</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-a</side>
<serial-number>2973459</serial-number>
<printed-timestamp>2021-03-23T09:29:37.992-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy001-frame000002</universal-frame-id>
<copy-number>1</copy-number>
<copy-relative-frame-number>2</copy-relative-frame-number>
<copy-relative-signature-number>1</copy-relative-signature-number>
<side>side-b</side>
<serial-number>297456</serial-number>
<printed-timestamp>2021-03-23T09:30:03.315-0700</printed-timestamp>
</side-b>
</start-range>
<end-range>
<sequence>1</sequence>
<side-a>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy004-frame000005</universal-frame-id>
<copy-number>4</copy-number>
<copy-relative-frame-number>5</copy-relative-frame-number>
<copy-relative-signature-number>3</copy-relative-signature-number>
<side>side-a</side>
<serial-number>297421</serial-number>
<printed-timestamp>2021-03-23T09:29:39.620-0700</printed-timestamp>
</side-a>
<side-b>
<universal-frame-id>18\'\'_DIVIDER_SS_Books_TOP.s2224-copy004-frame000006</universal-frame-id>
<copy-number>4</copy-number>
<copy-relative-frame-number>6</copy-relative-frame-number>
<copy-relative-signature-number>3</copy-relative-signature-number>
<side>side-b</side>
<serial-number>297422</serial-number>
<printed-timestamp>2021-03-23T09:30:04.178-0700</printed-timestamp>
</side-b>
</end-range>
<start-printing>
<sequence>2</sequence>
<timestamp>2021-03-23T09:29:37.996-0700</timestamp>
</start-printing>
<start-printing>
<sequence>3</sequence>
<timestamp>2021-03-23T09:29:51.743-0700</timestamp>
</start-printing>
<end-printing>
<sequence>4</sequence>
<timestamp>2021-03-23T09:30:04.179-0700</timestamp>
<position units=\"inches\">471405.92833333334</position>
</end-printing>
</job-manifest>
<content-metadata/>
</job>
</jobs>
</message>
"


| spath path=message.jobs output=jobs
| spath input=jobs path=job output=job
| fields - _raw _time jobs
| mvexpand job
| spath input=job path=customer-job-id
| spath input=job path=job-manifest.start-range.side-a.serial-number output=start-range.side-a.serial-number
| spath input=job path=job-manifest.start-range.side-b.serial-number output=start-range.side-b.serial-number
| spath input=job path=job-manifest.end-range.side-a.serial-number output=end-range.side-a.serial-number
| spath input=job path=job-manifest.end-range.side-b.serial-number output=end-range.side-b.serial-number
| fields customer-job-id start-range.side-a.serial-number start-range.side-b.serial-number end-range.side-a.serial-number end-range.side-b.serial-number
| eval start-range.side-a.serial-number=mvindex('start-range.side-a.serial-number',0)
| eval start-range.side-b.serial-number=mvindex('start-range.side-b.serial-number',0)
| eval end-range.side-a.serial-number=mvindex('end-range.side-a.serial-number',0)
| eval end-range.side-b.serial-number=mvindex('end-range.side-b.serial-number',0)