Extract execution plan in XML format to table

I'm trying to extract the data from SQL server execution plans in a generic way.

As an example the execution plan for

SELECT *
FROM sys.all_objects o1

as shown in SSMS is below

The UI shows nodes along with costs for each node and percentages. How can I extract this from the underlying XML into a table structure?

I've tried to query the XML by my self, but it seems that the XML structure is changing from query to query.

This should get you started (DB Fiddle example).

DECLARE @X XML = N'<?xml version="1.0" encoding="utf-16"?><ShowPlanXML ...';
        
DECLARE @Nodes TABLE
  (
     PlanId                    INT,
     NodeId                    INT,
     PhysicalOp                VARCHAR(200),
     EstimatedTotalSubtreeCost FLOAT,
     EstimatedOperatorCost     FLOAT,
     ParentNodeId              INT NULL,
     PRIMARY KEY(PlanId, NodeId)
  ); 
 
WITH  XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plans AS
(
SELECT ROW_NUMBER() over (order by qp) as PlanId, qp.query('.') as plan_xml
FROM @X.nodes('//QueryPlan') n(qp)
)
INSERT @Nodes(PlanId, NodeId, PhysicalOp, EstimatedTotalSubtreeCost, ParentNodeId) 
SELECT PlanId, 
       NodeId = relop.value('@NodeId', 'int'), 
       PhysicalOp = relop.value('@PhysicalOp', 'varchar(200)'), 
       EstimatedTotalSubtreeCost = relop.value('@EstimatedTotalSubtreeCost', 'float'),
	   /*XPath ancestor axis not supported so just go up a few levels and look for the closest ancestor Relop*/
	   ParentNodeId = COALESCE(
	   relop.value('..[local-name() = "RelOp"]/@NodeId', 'int'),
	   relop.value('../..[local-name() = "RelOp"]/@NodeId', 'int'),
	   relop.value('../../..[local-name() = "RelOp"]/@NodeId', 'int'),
	   relop.value('../../../..[local-name() = "RelOp"]/@NodeId', 'int')
	   )
FROM plans
CROSS APPLY plan_xml.nodes('//RelOp') n(relop);

UPDATE N1
SET EstimatedOperatorCost = EstimatedTotalSubtreeCost -  ISNULL((SELECT SUM(EstimatedTotalSubtreeCost) FROM @Nodes N2 WHERE N1.PlanId = N2.PlanId AND N2.ParentNodeId = N1.NodeId),0)
FROM @Nodes N1


SELECT *,
       EstPctOperatorCost = FORMAT(EstimatedOperatorCost/MAX(EstimatedTotalSubtreeCost) OVER (PARTITION BY PlanId), 'P0')
FROM @Nodes

The execution plan is a tree - there are likely more elegant ways of getting the parent operator than my attempt!

The above is not battle tested across a sample size of more than two execution plans so you may well encounter issues with it that you will need to fix.

You can visit the URI http://schemas.microsoft.com/sqlserver/2004/07/showplan to see information about the various schemas though for some reason I've never got to the bottom of it displays "The request is blocked." for me unless I use incognito mode.