-1

I have some XML that I need to turn into structured data in Python. I am needing to know how to access each element of the XML below. The example I used there worked with another XML file.

I guess I need to know more about the root and an element tree and how to access everything in the XML file below. Code examples are appreciated.

Code I have tried (the line myroot.findall('opsReports/opsReport') does not have any data in it):

import xml.etree.ElementTree as ET

mytree = ET.parse('C:\\Users\\uname\\Documents\\RPA\\PGE_DOS_K_10H_WITSML\\OpsReport_7_28_2022.xml')
myroot = mytree.getroot()
print(myroot)

reportsArray = []

# iterate over all the nodes with tag name - reports/report
for reports in myroot.findall('opsReports/opsReport'):    #<-- This line fails
    # access all elements in node
    for element in reports:
        ele_name = element.tag
        ele_value = reports.find(element.tag).text
        print(ele_name, ' : ', ele_value)

XML Data:

<?xml version="1.0" encoding="utf-8"?>
<opsReports xmlns="http://www.witsml.org/schemas/131" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.witsml.org/schemas/131  ../obj_opsReport.xsd" version="1.3.1.1">
  <documentInfo>
    <DocumentName>opsReport</DocumentName>
    <FileCreationInformation>
      <FileCreationDate>2022-08-31T16:56:36Z</FileCreationDate>
      <SoftwareName>MUDPRO+</SoftwareName>
      <FileCreator>DDye</FileCreator>
    </FileCreationInformation>
  </documentInfo>
  <opsReport uidWell="PGE_DOS_K_10H" uidWellbore="3_-_Prod._Vertical" uid="1">
    <nameWell>PGE DOS K 10H</nameWell>
    <nameWellbore>3_-_Prod._Vertical</nameWellbore>
    <name>1</name>
    <rig>Nabors 890</rig>
    <dTim>2022-07-29T01:00:00Z</dTim>
    <eTimSpud uom="d">72</eTimSpud>
    <mdReport uom="ft">2672.00</mdReport>
    <tvdReport uom="ft">2672.00</tvdReport>
    <distDrill uom="ft">2672.00</distDrill>
    <engineer>Mike Francis</engineer>
    <activity uid="act-1">
      <phase>3 - Prod. Vertical</phase>
      <operator>CHK</operator>
    </activity>
    <drillingParams uid="dp-1">
      <eTimOpBit uom="h">0</eTimOpBit>
      <mdHoleStop uom="ft">2672.00</mdHoleStop>
      <wtMud uom="lbm/galUS">12.10</wtMud>
    </drillingParams>
    <wbGeometry>
      <dTimReport>2022-07-29T01:00:00Z</dTimReport>
      <mdBottom uom="ft">2672.00</mdBottom>
      <wbGeometrySection uid="wbsection-1">
        <typeHoleCasing>casing</typeHoleCasing>
        <mdTop uom="ft">0.00</mdTop>
        <mdBottom uom="ft">2672.00</mdBottom>
        <idSection uom="in">8.921</idSection>
        <odSection uom="in">9.625</odSection>
      </wbGeometrySection>
      <wbGeometrySection uid="wbsection-12">
        <typeHoleCasing>open hole</typeHoleCasing>
        <idSection uom="in">8.750</idSection>
      </wbGeometrySection>
    </wbGeometry>
    <dayCost uid="dc-1">
      <costClass>mud</costClass>
      <costCode>mud product</costCode>
      <costItemDescription>daily mud product cost</costItemDescription>
      <costAmount currency="$">0.00</costAmount>
    </dayCost>
    <dayCost uid="dc-2">
      <costClass>mud</costClass>
      <costCode>mud package</costCode>
      <costItemDescription>daily mud package cost</costItemDescription>
      <costAmount currency="$">0.00</costAmount>
    </dayCost>
    <dayCost uid="dc-3">
      <costClass>mud</costClass>
      <costCode>mud service</costCode>
      <costItemDescription>daily mud service cost</costItemDescription>
      <costAmount currency="$">90.00</costAmount>
    </dayCost>
    <dayCost uid="dc-4">
      <costClass>mud</costClass>
      <costCode>mud engineering</costCode>
      <costItemDescription>daily mud engineering cost</costItemDescription>
      <costAmount currency="$">950.00</costAmount>
    </dayCost>
    <dayCost uid="dc-5">
      <costClass>mud</costClass>
      <costCode>mud tax</costCode>
      <costItemDescription>daily mud tax cost</costItemDescription>
      <costAmount currency="$">72.80</costAmount>
    </dayCost>
    <fluid uid="fluid-1">
      <type>Oil based</type>
      <locationSample>Suction</locationSample>
      <dTim>2022-07-28T13:00:00Z</dTim>
      <md uom="ft">2672.00</md>
      <density uom="lbm/galUS">12.10</density>
      <visFunnel uom="s">85.0</visFunnel>
      <tempVis uom="degF">150.0</tempVis>
      <pv uom="cP">37.0</pv>
      <yp uom="lbf/100ft2">9.0</yp>
      <gel10Sec uom="lbf/100ft2">7.0</gel10Sec>
      <gel10Min uom="lbf/100ft2">15.0</gel10Min>
      <gel30Min uom="lbf/100ft2">22.0</gel30Min>
      <tempHthp uom="degF">250.0</tempHthp>
      <filtrateHthp uom="mL">7.80</filtrateHthp>
      <filterCakeHthp uom="in/32">3</filterCakeHthp>
      <solidsPc uom="%">29.00</solidsPc>
      <waterPc uom="%">12.00</waterPc>
      <oilPc uom="%">59.00</oilPc>
      <solidsLowGravPc uom="%">22.61</solidsLowGravPc>
      <solidsCalcPc uom="%">27.84</solidsCalcPc>
      <baritePc uom="%">5.23</baritePc>
      <lcm uom="lbm/bbl">0.00</lcm>
      <chloride uom="mg/L">28000</chloride>
      <calcium uom="mg/L">15828</calcium>
      <rheometer uid="rheometer-1">
        <tempRheom uom="degF">150.0</tempRheom>
        <vis3Rpm>4</vis3Rpm>
        <vis6Rpm>5</vis6Rpm>
        <vis100Rpm>20</vis100Rpm>
        <vis200Rpm>33</vis200Rpm>
        <vis300Rpm>46</vis300Rpm>
        <vis600Rpm>83</vis600Rpm>
      </rheometer>
      <lime uom="lbm/bbl">1.42</lime>
      <electStab uom="V">1115.00</electStab>
      <calciumChloride uom="mg/L">332895</calciumChloride>
      <company>Valence Drilling Fluids</company>
      <engineer>Mike Francis</engineer>
      <asg>2.88</asg>
      <solidsHiGravPc uom="%">5.23</solidsHiGravPc>
      <solCorPc uom="%">27.84</solCorPc>
      <comments>Recommended Tour Treatment: Maintain Mud Weight 11.0 APCO          Viscosity 55-65

Adding lime for alkalinity
Adding CACL2 for WPS
Adding XMP for HPHT control
Adding IN FORCE OW PLUS, S &amp; P for emulsion/wetting
Running Diesel 10 bph &amp; Water 3bph  for O/W Ratio &amp; help control LGS
Dusting up active with barite as needed while running centrifuge, diesel &amp; water
Remarks: Finished rig skid to 10H well. Nipple up BOPE and test. Finish rig up flow line and clean sand traps. R/U @ report time.</comments>
    </fluid>
    <fluid uid="fluid-2">
      <type>Oil based</type>
      <locationSample>Suction</locationSample>
      <dTim>2022-07-29T01:00:00Z</dTim>
      <md uom="ft">2672.00</md>
      <density uom="lbm/galUS">11.10</density>
      <visFunnel uom="s">75.0</visFunnel>
      <tempVis uom="degF">150.0</tempVis>
      <pv uom="cP">28.0</pv>
      <yp uom="lbf/100ft2">10.0</yp>
      <gel10Sec uom="lbf/100ft2">4.0</gel10Sec>
      <gel10Min uom="lbf/100ft2">10.0</gel10Min>
      <gel30Min uom="lbf/100ft2">16.0</gel30Min>
      <tempHthp uom="degF">250.0</tempHthp>
      <filtrateHthp uom="mL">8.60</filtrateHthp>
      <filterCakeHthp uom="in/32">3</filterCakeHthp>
      <solidsPc uom="%">22.00</solidsPc>
      <waterPc uom="%">15.00</waterPc>
      <oilPc uom="%">63.00</oilPc>
      <solidsLowGravPc uom="%">15.73</solidsLowGravPc>
      <solidsCalcPc uom="%">20.87</solidsCalcPc>
      <baritePc uom="%">5.14</baritePc>
      <lcm uom="lbm/bbl">0.00</lcm>
      <chloride uom="mg/L">28000</chloride>
      <calcium uom="mg/L">15828</calcium>
      <rheometer uid="rheometer-1">
        <tempRheom uom="degF">150.0</tempRheom>
        <vis3Rpm>3</vis3Rpm>
        <vis6Rpm>4</vis6Rpm>
        <vis100Rpm>16</vis100Rpm>
        <vis200Rpm>27</vis200Rpm>
        <vis300Rpm>38</vis300Rpm>
        <vis600Rpm>66</vis600Rpm>
      </rheometer>
      <lime uom="lbm/bbl">1.42</lime>
      <electStab uom="V">1010.00</electStab>
      <calciumChloride uom="mg/L">271659</calciumChloride>
      <company>Valence Drilling Fluids</company>
      <engineer>Mike Francis</engineer>
      <asg>2.97</asg>
      <solidsHiGravPc uom="%">5.14</solidsHiGravPc>
      <solCorPc uom="%">20.87</solCorPc>
      <comments>Recommended Tour Treatment: Maintain Mud Weight 11.0 APCO          Viscosity 55-65

Adding lime for alkalinity
Adding CACL2 for WPS
Adding XMP for HPHT control
Adding IN FORCE OW PLUS, S &amp; P for emulsion/wetting
Running Diesel 10 bph &amp; Water 3bph  for O/W Ratio &amp; help control LGS
Dusting up active with barite as needed while running centrifuge, diesel &amp; water
Remarks: Finished rig skid to 10H well. Nipple up BOPE and test. Finish rig up flow line and clean sand traps. R/U @ report time.</comments>
    </fluid>
    <pitVolume uid="pv-1">
      <pit uidRef="Active">1</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">250.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-2">
      <pit uidRef="Trip Tank">2</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-3">
      <pit uidRef="Slug Pit">3</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-4">
      <pit uidRef="Frac #1">4</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">294.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-5">
      <pit uidRef="Frac #2">5</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">493.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-6">
      <pit uidRef="Frac #3">6</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">486.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-7">
      <pit uidRef="Frac #4">7</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">478.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <pitVolume uid="pv-8">
      <pit uidRef="Frac #5">8</pit>
      <dTim>2022-08-31T16:56:36Z</dTim>
      <volPit uom="bbl">475.0</volPit>
      <densFluid uom="lbm/galUS">12.00</densFluid>
      <descFluid>OBM</descFluid>
    </pitVolume>
    <mudVolume>
      <volTotMudStart uom="bbl">0.0</volTotMudStart>
      <volMudDumped uom="bbl">349.0</volMudDumped>
      <volMudReceived uom="bbl">2776.0</volMudReceived>
      <volMudReturned uom="bbl">0.0</volMudReturned>
      <mudLosses>
        <volLostShakerSurf uom="bbl">0.0</volLostShakerSurf>
        <volLostMudCleanerSurf uom="bbl">0.0</volLostMudCleanerSurf>
        <volLostPitsSurf uom="bbl">0.0</volLostPitsSurf>
        <volLostTrippingSurf uom="bbl">0.0</volLostTrippingSurf>
        <volLostOtherSurf uom="bbl">349.0</volLostOtherSurf>
        <volTotMudLostSurf uom="bbl">349.0</volTotMudLostSurf>
        <volLostCircHole uom="bbl">0.0</volLostCircHole>
        <volLostCsgHole uom="bbl">0.0</volLostCsgHole>
        <volLostCmtHole uom="bbl">0.0</volLostCmtHole>
        <volLostBhdCsgHole uom="bbl">0.0</volLostBhdCsgHole>
        <volLostAbandonHole uom="bbl">0.0</volLostAbandonHole>
        <volLostOtherHole uom="bbl">0.0</volLostOtherHole>
        <volTotMudLostHole uom="bbl">0.0</volTotMudLostHole>
      </mudLosses>
      <volMudBuilt uom="bbl">599.0</volMudBuilt>
      <volMudString uom="bbl">0.0</volMudString>
      <volMudCasing uom="bbl">0.0</volMudCasing>
      <volMudHole uom="bbl">0.0</volMudHole>
      <volMudRiser uom="bbl">0.0</volMudRiser>
      <volTotMudEnd uom="bbl">250.0</volTotMudEnd>
    </mudVolume>
    <mudInventory uid="mi-1">
      <name>DIESEL, GAL</name>
      <itemVolPerUnit uom="galUS">1</itemVolPerUnit>
      <pricePerUnit currency="$">0.00</pricePerUnit>
      <qtyStart>0</qtyStart>
      <qtyAdjustment>0</qtyAdjustment>
      <qtyReceived>18210</qtyReceived>
      <qtyReturned>0</qtyReturned>
      <qtyUsed>2040</qtyUsed>
      <costItem currency="$">0.00</costItem>
      <qtyOnLocation>16170</qtyOnLocation>
    </mudInventory>
    <mudInventory uid="mi-2">
      <name>BARITE 4.1, 100#</name>
      <itemWtPerUnit uom="lbm">100</itemWtPerUnit>
      <pricePerUnit currency="$">14.50</pricePerUnit>
      <qtyStart>0</qtyStart>
      <qtyAdjustment>0</qtyAdjustment>
      <qtyReceived>78</qtyReceived>
      <qtyReturned>0</qtyReturned>
      <qtyUsed>0</qtyUsed>
      <costItem currency="$">0.00</costItem>
      <qtyOnLocation>78</qtyOnLocation>
    </mudInventory>
    <mudInventory uid="mi-3">
      <name>BARITE 4.1, TON</name>
      <itemWtPerUnit uom="tonUS">1</itemWtPerUnit>
      <pricePerUnit currency="$">235.00</pricePerUnit>
      <qtyStart>0</qtyStart>
      <qtyAdjustment>0</qtyAdjustment>
      <qtyReceived>70</qtyReceived>
      <qtyReturned>0</qtyReturned>
      <qtyUsed>0</qtyUsed>
      <costItem currency="$">0.00</costItem>
      <qtyOnLocation>70</qtyOnLocation>
    </mudInventory>
    <pumpOp uid="po-1">
      <pump uidRef="Pump-1">1</pump>
      <idLiner uom="in">5.000</idLiner>
      <lenStroke uom="in">12.000</lenStroke>
      <rateStroke uom="rpm">0</rateStroke>
      <pressure uom="psi">0</pressure>
      <pcEfficiency uom="%">95.00</pcEfficiency>
    </pumpOp>
    <pumpOp uid="po-2">
      <pump uidRef="Pump-2">2</pump>
      <idLiner uom="in">5.000</idLiner>
      <lenStroke uom="in">12.000</lenStroke>
      <rateStroke uom="rpm">0</rateStroke>
      <pressure uom="psi">0</pressure>
      <pcEfficiency uom="%">95.00</pcEfficiency>
    </pumpOp>
    <pumpOp uid="po-3">
      <pump uidRef="Pump-3">3</pump>
      <idLiner uom="in">5.000</idLiner>
      <lenStroke uom="in">12.000</lenStroke>
      <rateStroke uom="rpm">0</rateStroke>
      <pressure uom="psi">0</pressure>
      <pcEfficiency uom="%">95.00</pcEfficiency>
    </pumpOp>
    <costDayMud currency="$">1112.80</costDayMud>
    <nameFormation>Austin Chalk</nameFormation>
    <diaCsgLast uom="in">9.625</diaCsgLast>
    <mdCsgLast uom="ft">2672.00</mdCsgLast>
    <sum24Hr>Finished rig skid to 10H well. Nipple up BOPE and test. Finish rig up flow line and clean sand traps. R/U @ report time.</sum24Hr>
  </opsReport>
</opsReports>
YoYoYo
  • 17
  • 1
  • 7
  • 1
    Using `for reports in myroot.findall('{*}opsReport'):` should produce some output. See https://stackoverflow.com/a/20447459/407651. – mzjn Dec 07 '22 at 09:45
  • Ok that did get me some data. When I use: for reports in ```myroot.findall('.//{*}pumpOp'):``` to get the last 3 records in the XML file for pumpOp I am only getting one record while there are 3 in the XML file. Is there something else I need to do to get all the pumpOp and other records to be returned? – YoYoYo Dec 07 '22 at 15:50
  • I cannot reproduce that. – mzjn Dec 07 '22 at 15:56
  • You cannot reproduce the ```myroot.findall('.//{*}pumpOp')```? It is just the last 3 records in the XML file. You can use any section within pumpOp or other sections and it only returns 1 record. – YoYoYo Dec 07 '22 at 16:14
  • That's right. I cannot reproduce that particular problem. Perhaps you should ask a new question instead of adding comments here. What you are talking about now is not in the question. – mzjn Dec 07 '22 at 16:36
  • I said in the original post I need to know how to access each element of the XML document. Each meaning each and every element. ```for reports in myroot.findall('.//{*}mudInventory'):``` and ```for reports in myroot.findall('.//{http://www.witsml.org/schemas/131}mudInventory')``` only return 1 record when I am trying to get them all returned. – YoYoYo Dec 07 '22 at 19:02
  • If you need to access every element, I suggest `myroot.iter()`: https://docs.python.org/3/library/xml.etree.elementtree.html#xml.etree.ElementTree.Element.iter – mzjn Dec 08 '22 at 06:32

1 Answers1

0

I don't know what content you are interessted in. You can adjust the search, if you like. This example creates a CSV with TAG name TEXT of the tag and the ATTRIBUTES dictionary. Hope this helps you.

import timeit
import os, psutil, sys
import datetime

import pandas as pd
import xml.etree.ElementTree as ET
import re

class parse_xml:
    
    def __init__(self, file):
        """Create a CSV with TAG, TEXT and ATTRIBUTES dictionary"""
        self.file = file
        
        events = ["start", "end", "comment", "pi", "start-ns", "end-ns" ]
        tags_list = []
        namespaces = []
        data = []
        for event, elem in ET.iterparse(self.file, events=events):
            if event == "start-ns":
                #print(f'{event}: {elem}')
                namespaces.append({elem})
            if event == "end" and elem.tag not in tags_list:
                #print(f"{elem.tag}")
                tags_list.append(elem.tag)
                elem.clear()
            if event == "end":
                #print(elem.tag, elem.text, elem.attrib)
                row = (elem.tag, elem.text, elem.attrib)
                data.append(row)
                
                
        df = pd.DataFrame(data, columns=["TAG", "TEXT","ATTRIBUTES"])
        print(df.head)
        df.to_csv('opsReport.csv', index=False)
 
def main():
    xml_file = "opsReport.xml"
    parse_xml(xml_file)

if __name__ == "__main__":
    now = datetime.datetime.now()
    starttime = timeit.default_timer()
    main()
    process = psutil.Process(os.getpid())
    print('\nFinished')
    print(f"{now:%Y-%m-%d %H:%M}")
    print('Runtime:', timeit.default_timer()-starttime)
    print(f'RAM: {process.memory_info().rss/1000**2} MB')
    print(f'{sys.version}')

Output opsReport.csv, snap shot:

AG,TEXT,ATTRIBUTES
{http://www.witsml.org/schemas/131}DocumentName,,{}
{http://www.witsml.org/schemas/131}FileCreationDate,,{}
{http://www.witsml.org/schemas/131}SoftwareName,,{}
{http://www.witsml.org/schemas/131}FileCreator,,{}
{http://www.witsml.org/schemas/131}FileCreationInformation,,{}
{http://www.witsml.org/schemas/131}documentInfo,,{}
...
{http://www.witsml.org/schemas/131}idSection,8.750,{'uom': 'in'}
{http://www.witsml.org/schemas/131}wbGeometrySection,"
        ",{'uid': 'wbsection-12'}
Hermann12
  • 1,709
  • 2
  • 5
  • 14