topic Re: Transform Hierarchical XML into Table Rows in Dashboards & Visualizations

Transform Hierarchical XML into Table Rows

pixeldude — Thu, 15 Apr 2021 16:57:11 GMT

Hi everyone. I have some hierarchical XML that can get rather large. If you're familiar with SQL - I'm visualizing Execution Plans. I have data that looks like this:

<PlanXml> <CursorInformation> <SQL_ID>00j24vf2v5uay</SQL_ID> <ADDRESS>00000000B9AF2A00</ADDRESS> <HASH_VALUE>2243094878</HASH_VALUE> <PLAN_HASH_VALUE>3599761032</PLAN_HASH_VALUE> <CHILD_ADDRESS>00000000B9AF12A8</CHILD_ADDRESS> <CHILD_NUMBER>0</CHILD_NUMBER> <TIMESTAMP>2021-04-15 07:19:52</TIMESTAMP> </CursorInformation><CursorDetails> <ID>0</ID> <PARENT_ID></PARENT_ID> <DEPTH>0</DEPTH> <POSITION>1548</POSITION> <OPERATION>SELECT STATEMENT</OPERATION> <OPTIONS></OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS></OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER>ALL_ROWS</OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1548</COST> <CARDINALITY></CARDINALITY> <BYTES></BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST></CPU_COST> <IO_COST></IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION></PROJECTION> <TIME></TIME> <QBLOCK_NAME></QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>1</ID> <PARENT_ID>0</PARENT_ID> <DEPTH>1</DEPTH> <POSITION>1</POSITION> <OPERATION>SORT</OPERATION> <OPTIONS>AGGREGATE</OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS></OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST></COST> <CARDINALITY>1</CARDINALITY> <BYTES>13</BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST></CPU_COST> <IO_COST></IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION><![CDATA[(#keys=0) SUM("UI_COUNT")~22~]]></PROJECTION> <TIME></TIME> <QBLOCK_NAME>SEL$1</QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>2</ID> <PARENT_ID>1</PARENT_ID> <DEPTH>2</DEPTH> <POSITION>1</POSITION> <OPERATION>VIEW</OPERATION> <OPTIONS></OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS>"from$_subquery$_001"@"SEL$1"</OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1548</COST> <CARDINALITY>1</CARDINALITY> <BYTES>13</BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST>327951516</CPU_COST> <IO_COST>1541</IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION><![CDATA["STUFF"~NUMBER,22~]]></PROJECTION> <TIME>1</TIME> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>3</ID> <PARENT_ID>2</PARENT_ID> <DEPTH>3</DEPTH> <POSITION>1</POSITION> <OPERATION>SORT</OPERATION> <OPTIONS>AGGREGATE</OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS></OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST></COST> <CARDINALITY>1</CARDINALITY> <BYTES>37</BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST></CPU_COST> <IO_COST></IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION><![CDATA[(#keys=0) COUNT(*)~22~]]></PROJECTION> <TIME></TIME> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>4</ID> <PARENT_ID>3</PARENT_ID> <DEPTH>4</DEPTH> <POSITION>1</POSITION> <OPERATION>FILTER</OPERATION> <OPTIONS></OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS></OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST></COST> <CARDINALITY></CARDINALITY> <BYTES></BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST></CPU_COST> <IO_COST></IO_COST> <TEMP_SPACE></T EMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES><![CDATA[( IS NOT NULL OR IS NOT NULL OR ("MASTER"."DATA" IS NULL AND IS NOT NULL))]]></FILTER_PREDICATES> <PROJECTION></PROJECTION> <TIME></TIME> <QBLOCK_NAME></QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>5</ID> <PARENT_ID>4</PARENT_ID> <DEPTH>5</DEPTH> <POSITION>1</POSITION> <OPERATION>HASH JOIN</OPERATION> <OPTIONS></OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER></OBJECT_NUMBER> <OBJECT_OWNER></OBJECT_OWNER> <OBJECT_NAME></OBJECT_NAME> <OBJECT_ALIAS></OBJECT_ALIAS> <OBJECT_TYPE></OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1019</COST> <CARDINALITY>1312</CARDINALITY> <BYTES>48544</BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST>326015926</CPU_COST> <IO_COST>1012</IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES><![CDATA["MASTER"."DATA"="TYPE_REF"."DATA"]]></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION><![CDATA[(#keys=1) "MASTER"."STUFF"~VARCHAR2,10~, "MASTER"."CT_PRIMARY_KEY2"~VARCHAR2,10~, "MASTER"."CT_PRIMARY_KEY"~VARCHAR2,15~]]></PROJECTION> <TIME>1</TIME> <QBLOCK_NAME></QBLOCK_NAME> </CursorDetails><CursorDetails> <ID>6</ID> <PARENT_ID>5</PARENT_ID> <DEPTH>6</DEPTH> <POSITION>1</POSITION> <OPERATION>TABLE ACCESS</OPERATION> <OPTIONS>BY INDEX ROWID BATCHED</OPTIONS> <OBJECT_NODE></OBJECT_NODE> <OBJECT_NUMBER>81015</OBJECT_NUMBER> <OBJECT_OWNER>OWNER</OBJECT_OWNER> <OBJECT_NAME>Table</OBJECT_NAME> <OBJECT_ALIAS>"TYPE_REF"@"SEL$2"</OBJECT_ALIAS> <OBJECT_TYPE>TABLE</OBJECT_TYPE> <OPTIMIZER></OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>2</COST> <CARDINALITY>15</CARDINALITY> <BYTES>150</BYTES> <OTHER_TAG></OTHER_TAG> <DISTRIBUTION></DISTRIBUTION> <CPU_COST>20393</CPU_COST> <IO_COST>2</IO_COST> <TEMP_SPACE></TEMP_SPACE> <ACCESS_PREDICATES></ACCESS_PREDICATES> <FILTER_PREDICATES></FILTER_PREDICATES> <PROJECTION><![CDATA["TYPE_REF"."TABLE"~VARCHAR2,5~]]></PROJECTION> <TIME>1</TIME> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails> </PlanXml>

I want the data to come out looking tabular, like this:

SQL_ID	CHILD_NUMBER	TIMESTAMP	OPERATION	OPTION	ID
00j24vf2v5uay	0	2021-04-15 07:19:52	SELECT STATEMENT		0
00j24vf2v5uay	0	2021-04-15 07:19:52	SORT	AGGREGATE	1
etc...	etc...	etc..	etc..	etc..	etc..

I can get the data to come out into a table, but each row contains a subset of the parent, and the detail values don't line up from a readable perspective: I don't know what value in one column matches to a value in an adjacent column.

This is the command I was working with:

source=*PLANXMLTEST.xml | table PlanXml.CursorInformation.SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP,PlanXml.CursorDetails.ID,PlanXml.CursorDetails.OPERATION,PlanXml.CursorDetails.OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER,PlanXml.CursorDetails.COST,PlanXml.CursorDetails.CARDINALITY,PlanXml.CursorDetails.BYTES,PlanXml.CursorDetails.DISTRIBUTION,PlanXml.CursorDetails.CPU_COST,PlanXml.CursorDetails.IO_COST,PlanXml.CursorDetails.TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION | rename PlanXml.CursorInformation.SQL_ID AS SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE AS PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER AS CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP AS TIMESTAMP,PlanXml.CursorDetails.ID AS ID,PlanXml.CursorDetails.OPERATION as OPERATION,PlanXml.CursorDetails.OPTIONS AS OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER AS OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME AS OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE AS OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER AS OPTIMIZER,PlanXml.CursorDetails.COST AS COST,PlanXml.CursorDetails.CARDINALITY AS CARDINALITY,PlanXml.CursorDetails.BYTES AS BYTES,PlanXml.CursorDetails.DISTRIBUTION AS DISTRIBUTION,PlanXml.CursorDetails.CPU_COST AS CPU_COST,PlanXml.CursorDetails.IO_COST AS IO_COST,PlanXml.CursorDetails.TEMP_SPACE AS TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES AS ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES AS FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION AS PROJECTION

Let me know if I can provide any additional info - thanks for the help! 😊

Re: Transform Hierarchical XML into Table Rows

ITWhisperer — Thu, 15 Apr 2021 19:04:11 GMT

If you break out the CursorDetails collection into separate events, it is easier to keep track of what goes with what

| makeresults | eval _raw="<PlanXml> <CursorInformation> <SQL_ID>00j24vf2v5uay</SQL_ID> <ADDRESS>00000000B9AF2A00</ADDRESS> <HASH_VALUE>2243094878</HASH_VALUE> <PLAN_HASH_VALUE>3599761032</PLAN_HASH_VALUE> <CHILD_ADDRESS>00000000B9AF12A8</CHILD_ADDRESS> <CHILD_NUMBER>0</CHILD_NUMBER> <TIMESTAMP>2021-04-15 07:19:52</TIMESTAMP> </CursorInformation> <CursorDetails> <ID>0</ID> <PARENT_ID/> <DEPTH>0</DEPTH> <POSITION>1548</POSITION> <OPERATION>SELECT STATEMENT</OPERATION> <OPTIONS/> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS/> <OBJECT_TYPE/> <OPTIMIZER>ALL_ROWS</OPTIMIZER> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1548</COST> <CARDINALITY/> <BYTES/> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST/> <IO_COST/> <TEMP_SPACE/> <ACCESS_PREDICATES/> <FILTER_PREDICATES/> <PROJECTION/> <TIME/> <QBLOCK_NAME/> </CursorDetails> <CursorDetails> <ID>1</ID> <PARENT_ID>0</PARENT_ID> <DEPTH>1</DEPTH> <POSITION>1</POSITION> <OPERATION>SORT</OPERATION> <OPTIONS>AGGREGATE</OPTIONS> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS/> <OBJECT_TYPE/> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST/> <CARDINALITY>1</CARDINALITY> <BYTES>13</BYTES> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST/> <IO_COST/> <TEMP_SPACE/> <ACCESS_PREDICATES/> <FILTER_PREDICATES/> <PROJECTION> <![CDATA[(#keys=0) SUM(\"UI_COUNT\")~22~]]> </PROJECTION> <TIME/> <QBLOCK_NAME>SEL$1</QBLOCK_NAME> </CursorDetails> <CursorDetails> <ID>2</ID> <PARENT_ID>1</PARENT_ID> <DEPTH>2</DEPTH> <POSITION>1</POSITION> <OPERATION>VIEW</OPERATION> <OPTIONS/> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS>"from$_subquery$_001"@"SEL$1"</OBJECT_ALIAS> <OBJECT_TYPE/> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1548</COST> <CARDINALITY>1</CARDINALITY> <BYTES>13</BYTES> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST>327951516</CPU_COST> <IO_COST>1541</IO_COST> <TEMP_SPACE/> <ACCESS_PREDICATES/> <FILTER_PREDICATES/> <PROJECTION> <![CDATA[\"STUFF\"~NUMBER,22~]]> </PROJECTION> <TIME>1</TIME> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails> <CursorDetails> <ID>3</ID> <PARENT_ID>2</PARENT_ID> <DEPTH>3</DEPTH> <POSITION>1</POSITION> <OPERATION>SORT</OPERATION> <OPTIONS>AGGREGATE</OPTIONS> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS/> <OBJECT_TYPE/> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST/> <CARDINALITY>1</CARDINALITY> <BYTES>37</BYTES> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST/> <IO_COST/> <TEMP_SPACE/> <ACCESS_PREDICATES/> <FILTER_PREDICATES/> <PROJECTION> <![CDATA[(#keys=0) COUNT(*)~22~]]> </PROJECTION> <TIME/> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails> <CursorDetails> <ID>4</ID> <PARENT_ID>3</PARENT_ID> <DEPTH>4</DEPTH> <POSITION>1</POSITION> <OPERATION>FILTER</OPERATION> <OPTIONS/> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS/> <OBJECT_TYPE/> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST/> <CARDINALITY/> <BYTES/> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST/> <IO_COST/> <TEMP_SPACE> </TEMP_SPACE> <ACCESS_PREDICATES/> <FILTER_PREDICATES> <![CDATA[( IS NOT NULL OR IS NOT NULL OR (\"MASTER\".\"DATA\" IS NULL AND IS NOT NULL))]]> </FILTER_PREDICATES> <PROJECTION/> <TIME/> <QBLOCK_NAME/> </CursorDetails> <CursorDetails> <ID>5</ID> <PARENT_ID>4</PARENT_ID> <DEPTH>5</DEPTH> <POSITION>1</POSITION> <OPERATION>HASH JOIN</OPERATION> <OPTIONS/> <OBJECT_NODE/> <OBJECT_NUMBER/> <OBJECT_OWNER/> <OBJECT_NAME/> <OBJECT_ALIAS/> <OBJECT_TYPE/> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>1019</COST> <CARDINALITY>1312</CARDINALITY> <BYTES>48544</BYTES> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST>326015926</CPU_COST> <IO_COST>1012</IO_COST> <TEMP_SPACE/> <ACCESS_PREDICATES> <![CDATA[\"MASTER\".\"DATA\"=\"TYPE_REF\".\"DATA\"]]> </ACCESS_PREDICATES> <FILTER_PREDICATES/> <PROJECTION> <![CDATA[(#keys=1) \"MASTER\".\"STUFF\"~VARCHAR2,10~, \"MASTER\".\"CT_PRIMARY_KEY2\"~VARCHAR2,10~, \"MASTER\".\"CT_PRIMARY_KEY\"~VARCHAR2,15~]]> </PROJECTION> <TIME>1</TIME> <QBLOCK_NAME/> </CursorDetails> <CursorDetails> <ID>6</ID> <PARENT_ID>5</PARENT_ID> <DEPTH>6</DEPTH> <POSITION>1</POSITION> <OPERATION>TABLE ACCESS</OPERATION> <OPTIONS>BY INDEX ROWID BATCHED</OPTIONS> <OBJECT_NODE/> <OBJECT_NUMBER>81015</OBJECT_NUMBER> <OBJECT_OWNER>OWNER</OBJECT_OWNER> <OBJECT_NAME>Table</OBJECT_NAME> <OBJECT_ALIAS>"TYPE_REF"@"SEL$2"</OBJECT_ALIAS> <OBJECT_TYPE>TABLE</OBJECT_TYPE> <OPTIMIZER/> <SEARCH_COLUMNS>0</SEARCH_COLUMNS> <COST>2</COST> <CARDINALITY>15</CARDINALITY> <BYTES>150</BYTES> <OTHER_TAG/> <DISTRIBUTION/> <CPU_COST>20393</CPU_COST> <IO_COST>2</IO_COST> <TEMP_SPACE/> <ACCESS_PREDICATES/> <FILTER_PREDICATES/> <PROJECTION> <![CDATA[\"TYPE_REF\".\"TABLE\"~VARCHAR2,5~]]> </PROJECTION> <TIME>1</TIME> <QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME> </CursorDetails> </PlanXml>" | spath path=PlanXml.CursorDetails output=CursorDetails | fields - _* | mvexpand CursorDetails | spath input=CursorDetails | fields - CursorDetails

Re: Transform Hierarchical XML into Table Rows

pixeldude — Thu, 15 Apr 2021 19:22:29 GMT

Thank you for your reply! That makes sense to me, but trying to get it formatted correctly in a table is really proving difficult. Am I missing something?

source=*PLANXMLTEST.xml PlanXml.CursorInformation.SQL_ID=00j24vf2v5uay| spath path=PlanXml.CursorDetails output=CursorDetails | fields - _* | mvexpand CursorDetails | spath input=CursorDetails | fields - CursorDetails | table PlanXml.CursorInformation.SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP,PlanXml.CursorDetails.ID,PlanXml.CursorDetails.OPERATION,PlanXml.CursorDetails.OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER,PlanXml.CursorDetails.COST,PlanXml.CursorDetails.CARDINALITY,PlanXml.CursorDetails.BYTES,PlanXml.CursorDetails.DISTRIBUTION,PlanXml.CursorDetails.CPU_COST,PlanXml.CursorDetails.IO_COST,PlanXml.CursorDetails.TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION | rename PlanXml.CursorInformation.SQL_ID AS SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE AS PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER AS CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP AS TIMESTAMP,PlanXml.CursorDetails.ID AS ID,PlanXml.CursorDetails.OPERATION as OPERATION,PlanXml.CursorDetails.OPTIONS AS OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER AS OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME AS OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE AS OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER AS OPTIMIZER,PlanXml.CursorDetails.COST AS COST,PlanXml.CursorDetails.CARDINALITY AS CARDINALITY,PlanXml.CursorDetails.BYTES AS BYTES,PlanXml.CursorDetails.DISTRIBUTION AS DISTRIBUTION,PlanXml.CursorDetails.CPU_COST AS CPU_COST,PlanXml.CursorDetails.IO_COST AS IO_COST,PlanXml.CursorDetails.TEMP_SPACE AS TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES AS ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES AS FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION AS PROJECTION | sort TIMESTAMP,SQL_ID,CHILD_NUMBER,ID

Now I just get duplicate rows with all the data still out of whack:

Re: Transform Hierarchical XML into Table Rows

ITWhisperer — Thu, 15 Apr 2021 19:34:02 GMT

It looks like you still have all the fields that we (un)helpfully extracted for you when the file was indexed - try removing everything apart from the _raw field (you may want to go back to the config that ingested the data in the first place to make it more usable), also extract fields from CursorInformation

Re: Transform Hierarchical XML into Table Rows

pixeldude — Thu, 15 Apr 2021 19:42:32 GMT

YES - that seems to have me headed in the right direction now! Thank you SO much!

I'm curious when you say go back to the config and make it more usable... Can you provide any direction there? It took me a good amount of research to get the events to parse correctly to begin with - any direction would be greatly appreciated 😊

Re: Transform Hierarchical XML into Table Rows

ITWhisperer — Thu, 15 Apr 2021 20:04:21 GMT

To be honest, I usually leave that part to others, having said that, it appears that the extraction process has done a blanket extract of the XML and has lost the hierarchy in the process. Although, it might be worth checking to see if the extract has the hierarchy, e.g. do you have a field that contains all the CursorInformation fields and similarly as field that contains all the CursorDetails either as a multivalue field or raw XML? If so, this would save you extracting it again from the _raw.