Im having issues parsing out each child node within an xml file. The number of nodes can change per Instrument_Root. For instance, Instrument_Watch is NULL here, but will be populated in other instances after this. My goal is to have each child node parsed individually (Instrument_Ratings, Instrument_Attribute_Ratings, Instrument_Organization, Instrument_Supports, etc.)
I tried doing the following, but it just returned the first intance repeatedly - there are 3700 Instrument_Root in the file, and Instrument_Rating for this one Instrument_Root was repeated 3700 times. I also ran into errors with etree due to the namespace.
from lxml import objectify
xml = objectify.parse(file)
root = xml.getroot()
tree1 = []
tree2 = []
tree3 = []
tree4 = []
for children in range(len(root.getchildren())):
tree1.append([child.text for child in root.getchildren()[children].iterchildren()])
for children2 in root.Instrument_Root.Instrument_Ratings.Instrument_Rating.getchildren():
tree2.append([child2.text for child2 in root.Instrument_Root.Instrument_Ratings.Instrument_Rating.getchildren()])
for children3 in root.Instrument_Root.Instrument_Ratings.Instrument_Rating.Instrument_Rating_Attributes.Instrument_Rating_Attribute.getchildren():
tree3.append([child3.text for child3 in root.Instrument_Root.Instrument_Ratings.Instrument_Rating.Instrument_Rating_Attributes.Instrument_Rating_Attribute.getchildren()])
for children4 in root.Instrument_Root.Instrument_Organizations.Instrument_Organization.getchildren():
tree4.append([child4.text for child4 in root.Instrument_Root.Instrument_Organizations.Instrument_Organization.getchildren()])
XML:
<?xml version="1.0" encoding="utf-8"?>
<Instrument_Roots xmlns="http://www.XXXXX.com" xmlns:xsi="http://www.XXXXXXX.XMLSchema-instance" file_type="Baseline" frequency="Hourly-12" generation_time="2020-04-06T12:00:00Z">
<Instrument_Root>
<Instrument_ID>831295951</Instrument_ID>
<Deal_number>831275547</Deal_number>
<Class_Code>18705</Class_Code>
<Class_Text>Pass-Through</Class_Text>
<Class_Short_Description>PAS</Class_Short_Description>
<Dated_Date>2020-03-21T00:00:00</Dated_Date>
<ISO_Currency_Code>AUD</ISO_Currency_Code>
<Currency_Multiple_Indicator>N</Currency_Multiple_Indicator>
<Maturity_Date>2051-03-21T00:00:00</Maturity_Date>
<Maturity_Year>2051</Maturity_Year>
<Sale_Date>2020-03-21T00:00:00</Sale_Date>
<Face_Amount_USD>7.2534316791</Face_Amount_USD>
<Credit_Linked_Indicator>N</Credit_Linked_Indicator>
<Takedown_Indicator>N</Takedown_Indicator>
<Security_Description>Class B</Security_Description>
<Instrument_Type_Code>24657</Instrument_Type_Code>
<Instrument_Type_Text>PASS-THRU CTFS</Instrument_Type_Text>
<Private_Placement_Code>24922</Private_Placement_Code>
<Private_Placement_Text>Not Applicable</Private_Placement_Text>
<Coupon_Type_Code>26</Coupon_Type_Code>
<Coupon_Type_Text>Floating</Coupon_Type_Text>
<Coupon_Type_Short_Description>FLT</Coupon_Type_Short_Description>
<Coupon_Frequency_Code>16</Coupon_Frequency_Code>
<Coupon_Frequency_Text>Monthly</Coupon_Frequency_Text>
<Coupon_Frequency_Short_Description>MON</Coupon_Frequency_Short_Description>
<Coupon_Rate xsi:nil="true"/>
<Instrument_Description xsi:nil="true"/>
<Product_Line_Description>MBS - Prime</Product_Line_Description>
<Series_Class_Text>Class B</Series_Class_Text>
<Market_Description>AUSTRALIA</Market_Description>
<Face_Amount>11.2500000000</Face_Amount>
<Structured_Finance_Indicator>Y</Structured_Finance_Indicator>
<Structured_Finance_Sequence_Number>3</Structured_Finance_Sequence_Number>
<Instrument_Ratings>
<Instrument_Rating>
<Instrument_ID>831295951</Instrument_ID>
<Rating_Class_Number>37203</Rating_Class_Number>
<Rating_Date>2020-03-02T01:30:03</Rating_Date>
<XXXX_Rating_ID>831295958</XXXX_Rating_ID>
<Rating_Level>I</Rating_Level>
<Rating_Class_Text>Senior Secured</Rating_Class_Text>
<Security_Class_Code>18705</Security_Class_Code>
<Security_Class_Text>Pass-Through</Security_Class_Text>
<Security_Class_Short_Description>PAS</Security_Class_Short_Description>
<Duration_Code>25636</Duration_Code>
<Duration_Text>Long-Term Debt Rating</Duration_Text>
<Duration_Short_Description>LT</Duration_Short_Description>
<Seniority_Code>18743</Seniority_Code>
<Seniority_Text>Senior Secured</Seniority_Text>
<Seniority_Short_Description>SS</Seniority_Short_Description>
<Evaluation_Type_Code>25648</Evaluation_Type_Code>
<Evaluation_Type_Text>Credit Risk</Evaluation_Type_Text>
<Shadow_Code>5734</Shadow_Code>
<Shadow_Text>Enhanced</Shadow_Text>
<Shadow_Short_Description>ENH</Shadow_Short_Description>
<Rating_Subclass_Code xsi:nil="true"/>
<Rating_Subclass_Text xsi:nil="true"/>
<Currency_Capd_Code>19142</Currency_Capd_Code>
<Currency_Capd_Text>Local Currency</Currency_Capd_Text>
<Rating_Text>NR</Rating_Text>
<Credit_Grade xsi:nil="true"/>
<Rating_Rank>0</Rating_Rank>
<Rating_Direction_Code>19102</Rating_Direction_Code>
<Rating_Direction_Text>DECISION NOT TO RATE</Rating_Direction_Text>
<Rating_Direction_Short_Description>NR</Rating_Direction_Short_Description>
<Rating_Type_Code>534</Rating_Type_Code>
<Rating_Type_Text>Long-Term Debt Rating</Rating_Type_Text>
<Rating_Type_Short_Description>LT</Rating_Type_Short_Description>
<Rating_Enhancement_Level>ENH</Rating_Enhancement_Level>
<Rating_Local_Date>2020-03-02T17:30:03</Rating_Local_Date>
<Rating_Termination_Date xsi:nil="true"/>
<Rating_Termination_Local_Date xsi:nil="true"/>
<Rating_Reason_Code>25530</Rating_Reason_Code>
<Rating_Reason_Text>DECISION NOT TO RATE</Rating_Reason_Text>
<Rating_Currency_Code>20525</Rating_Currency_Code>
<Rating_Currency_Text>Australian Dollar</Rating_Currency_Text>
<Rating_Currency_ISO_Code>AUD</Rating_Currency_ISO_Code>
<Rating_Monitor_Indicator>1</Rating_Monitor_Indicator>
<Initial_Rating_Indicator>Y</Initial_Rating_Indicator>
<Instrument_Watchlist xsi:nil="true"/>
<Instrument_Rating_Attributes>
</Instrument_Rating_Attributes>
</Instrument_Rating>
</Instrument_Ratings>
<Instrument_Supports xsi:nil="true"/>
<Instrument_Organizations>
<Instrument_Organization>
<Organization_ID>88100</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>5156484</Organization_Role_Code>
<Organization_Role_Text>Issuer Account Bank</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>435700</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600045234</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>25017</Organization_Role_Code>
<Organization_Role_Text>Seller</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600045234</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>25020</Organization_Role_Code>
<Organization_Role_Text>Servicer</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>530750</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>24998</Organization_Role_Code>
<Organization_Role_Text>Arranger</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>540500</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>714325</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>192835</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600020914</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>831295948</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>129</Organization_Role_Code>
<Organization_Role_Text>Issuer</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600018753</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>140</Organization_Role_Code>
<Organization_Role_Text>Trustee</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600045234</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>25002</Organization_Role_Code>
<Organization_Role_Text>Custodian</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>806942842</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>140</Organization_Role_Code>
<Organization_Role_Text>Trustee</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>820688318</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>5155837</Organization_Role_Code>
<Organization_Role_Text>Cash Manager</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>530750</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>141</Organization_Role_Code>
<Organization_Role_Text>Underwriter</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
<Instrument_Organization>
<Organization_ID>600045234</Organization_ID>
<Instrument_ID>831295951</Instrument_ID>
<Organization_Role_Code>25012</Organization_Role_Code>
<Organization_Role_Text>Originator</Organization_Role_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Organization>
</Instrument_Organizations>
<Instrument_Identifiers xsi:nil="true"/>
<Instrument_Attributes>
<Instrument_Attribute>
<Instrument_ID>831295951</Instrument_ID>
<Rating_Attribute_Type_Code>5156438</Rating_Attribute_Type_Code>
<Rating_Attribute_Type_Text>SF Indicator</Rating_Attribute_Type_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
<Rating_Attribute_Code>5156439</Rating_Attribute_Code>
<Rating_Attribute_Text>(sf)</Rating_Attribute_Text>
</Instrument_Attribute>
<Instrument_Attribute>
<Instrument_ID>831295951</Instrument_ID>
<Rating_Attribute_Type_Code>5168187</Rating_Attribute_Type_Code>
<Rating_Attribute_Type_Text>SEC Exempt</Rating_Attribute_Type_Text>
<Effective_Date>2020-03-02T01:30:03</Effective_Date>
<Termination_Date xsi:nil="true"/>
<Rating_Attribute_Code>5168185</Rating_Attribute_Code>
<Rating_Attribute_Text>Exempt</Rating_Attribute_Text>
</Instrument_Attribute>
</Instrument_Attributes>
<Instrument_Markets>
<Instrument_Market>
<Instrument_ID>831295951</Instrument_ID>
<Domain_Number>75300</Domain_Number>
<Effective_Date>2020-03-21T00:00:00</Effective_Date>
<Termination_Date xsi:nil="true"/>
</Instrument_Market>
</Instrument_Markets>
</Instrument_Root>
</Instrument_Roots>
Any ideas on how to attack this would be greatly appreciated. Thanks.
The source of your problem is that your XML has a default namespace
(http://www.XXXXX.com), so each attempt to locate an element must
include this namespace (your code failed on this detail).
To process your XML file I used the following code:
Import:
from lxml import etree as et
Read the XML file:
parser = et.XMLParser(remove_blank_text=True)
tree = et.parse('Instrum.xml', parser)
root = tree.getroot()
Define the namespace used:
ns = {'xx': 'http://www.XXXXX.com'}
(will be used below).
Fill tree1 with text content of children of each Instrument_Root:
tree1 = []
for elem in root.findall('xx:Instrument_Root/*', ns):
txt = elem.text
if txt is not None:
tree1.append(txt)
Note that Instrument_Root is a direct descendant of the root node, so it is enough to put just the node name.
Fill tree2 with text content of children of each Instrument_Rating:
tree2 = []
for elem in root.findall('.//xx:Instrument_Rating/*', ns):
txt = elem.text
if txt is not None and len(txt.strip()) > 0:
tree2.append(txt)
This time Instrument_Rating is located somewhere deeper in the XML tree, so XPath must include // to perform "all levels" search.
I added also some logic to avoid appending either non-existing text or text containing only "while" chars (delete it if you don't want to skip them).
For your XML input sample I got:
tree1:
['831295951', '831275547', '18705', 'Pass-Through', 'PAS', '2020-03-21T00:00:00',
'AUD', 'N', '2051-03-21T00:00:00', '2051', '2020-03-21T00:00:00', '7.2534316791',
'N', 'N', 'Class B', '24657', 'PASS-THRU CTFS', '24922', 'Not Applicable',
'26', 'Floating', 'FLT', '16', 'Monthly', 'MON', 'MBS - Prime', 'Class B',
'AUSTRALIA', '11.2500000000', 'Y', '3']
tree2:
['831295951', '37203', '2020-03-02T01:30:03', '831295958', 'I', 'Senior Secured',
'18705', 'Pass-Through', 'PAS', '25636', 'Long-Term Debt Rating', 'LT',
'18743', 'Senior Secured', 'SS', '25648', 'Credit Risk', '5734', 'Enhanced',
'ENH', '19142', 'Local Currency', 'NR', '0', '19102', 'DECISION NOT TO RATE',
'NR', '534', 'Long-Term Debt Rating', 'LT', 'ENH', '2020-03-02T17:30:03', '25530',
'DECISION NOT TO RATE', '20525', 'Australian Dollar', 'AUD', '1', 'Y']
Note that there is no need for any nested loops.
I think that based on the code above you will know how to extract content to fill tree3 and tree4.