Reading unstructured XML data

sasireka — Thu, 22 Apr 2021 07:40:14 GMT

<?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

Re: Reading unstructured XML data

ITWhisperer — Thu, 01 Apr 2021 12:30:32 GMT

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)

topic Re: Reading unstructured XML data in Dashboards & Visualizations

Reading unstructured XML data

Re: Reading unstructured XML data