I try to make a table (or csv, I'm using pandas dataframe) from the information of an XML file.

The file is here (.zip is 14 MB, XML is ~370MB), https://nvd.nist.gov/feeds/xml/cpe/dictionary/official-cpe-dictionary_v2.3.xml.zip . It has package information of different languages - node.js, python, java etc. aka, CPE 2.3 list by the US government org NVD.

this is how it looks like in the first 30 rows:

<cpe-list xmlns:config="http://scap.nist.gov/schema/configuration/0.1" xmlns="http://cpe.mitre.org/dictionary/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.3" xmlns:cpe-23="http://scap.nist.gov/schema/cpe-extension/2.3" xmlns:ns6="http://scap.nist.gov/schema/scap-core/0.1" xmlns:meta="http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2" xsi:schemaLocation="http://scap.nist.gov/schema/cpe-extension/2.3 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary-extension_2.3.xsd http://cpe.mitre.org/dictionary/2.0 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary_2.3.xsd http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2 https://scap.nist.gov/schema/cpe/2.1/cpe-dictionary-metadata_0.2.xsd http://scap.nist.gov/schema/scap-core/0.3 https://scap.nist.gov/schema/nvd/scap-core_0.3.xsd http://scap.nist.gov/schema/configuration/0.1 https://scap.nist.gov/schema/nvd/configuration_0.1.xsd http://scap.nist.gov/schema/scap-core/0.1 https://scap.nist.gov/schema/nvd/scap-core_0.1.xsd">
  <generator>
    <product_name>National Vulnerability Database (NVD)</product_name>
    <product_version>4.9</product_version>
    <schema_version>2.3</schema_version>
    <timestamp>2022-03-17T03:51:01.909Z</timestamp>
  </generator>
  <cpe-item name="cpe:/a:$0.99_kindle_books_project:$0.99_kindle_books:6::~~~android~~">
    <title xml:lang="en-US">$0.99 Kindle Books project $0.99 Kindle Books (aka com.kindle.books.for99) for android 6.0</title>
    <references>
      <reference href="https://play.google.com/store/apps/details?id=com.kindle.books.for99">Product information</reference>
      <reference href="https://docs.google.com/spreadsheets/d/1t5GXwjw82SyunALVJb2w0zi3FoLRIkfGPc7AMjRF0r4/edit?pli=1#gid=1053404143">Government Advisory</reference>
    </references>
    <cpe-23:cpe23-item name="cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:*"/>
  </cpe-item>

The tree structure of the XML file is quite simple, the root is 'cpe-list', the child element is 'cpe-item', and the grandchild elements are 'title', 'references' and 'cpe23-item'.

From 'title', I want the text in the element; From 'cpe23-item', I want the attribute 'name'; From 'references', I want the attributes 'href' from its great-grandchildren, 'reference'.

The dataframe should look like this:

  | cpe23_name   | title_text            | ref1  | ref2  | ref3  | ref_other
0 | 'cpe23name 1'| 'this is a python pkg'| 'url1'| 'url2'| NaN   | NaN
1 | 'cpe23name 2'| 'this is a java pkg'  | 'url1'| 'url2'| NaN   | NaN
... 

my code is here，finished in ~100sec：

import xml.etree.ElementTree as et

xtree = et.parse("official-cpe-dictionary_v2.3.xml")
xroot = xtree.getroot()

import time
start_time = time.time()

df_cols = ["cpe", "text", "vendor", "product", "version", "changelog", "advisory", 'others']

title      = '{http://cpe.mitre.org/dictionary/2.0}title'
ref        = '{http://cpe.mitre.org/dictionary/2.0}references'
cpe_item   = '{http://scap.nist.gov/schema/cpe-extension/2.3}cpe23-item'

p_cpe = None
p_text = None
p_vend = None
p_prod = None
p_vers = None
p_chan = None
p_advi = None
p_othe = None
rows = []

i = 0

while i < len(xroot):
    for elm in xroot[i]:
        if elm.tag == title:
                p_text = elm.text
                #assign p_text
       
        elif elm.tag == ref:
            for nn in elm:
                s = nn.text.lower()
                #check the lower text in refs
                
                if 'version' in s:
                    p_vers = nn.attrib.get('href')
                    #assign p_vers
                elif 'advisor' in s:
                    p_advi = nn.attrib.get('href')
                    #assign p_advi
                elif 'product' in s:
                    p_prod = nn.attrib.get('href')
                    #assign p_prod
                elif 'vendor' in s:
                    p_vend = nn.attrib.get('href')
                    #assign p_vend
                elif 'change' in s:
                    p_chan = nn.attrib.get('href')
                    #assign p_vend
                else:
                    p_othe = nn.attrib.get('href')
            
        elif elm.tag == cpe_item:
            p_cpe = elm.attrib.get("name")
            #assign p_cpe
           
        else:
            print(elm.tag)
    row = [p_cpe, p_text, p_vend, p_prod, p_vers, p_chan, p_advi, p_othe]
    rows.append(row)        
    
    p_cpe = None
    p_text = None
    p_vend = None
    p_prod = None
    p_vers = None
    p_chan = None
    p_advi = None
    p_othe = None
    print(len(rows)) #this shows how far I got during the running time
    i =1
    
out_df1 = pd.DataFrame(rows, columns = df_cols)# move this part outside the loop by removing the indent

print("---853k rows take %s seconds ---" % (time.time() - start_time))

• updated: the faster way is to move the 2nd last row out side the loop. Since 'rows' already get info in each loop, there is no need to make a new dataframe every time. the running time now is 136.0491042137146 seconds. yay!

CodePudding user response：

Since your XML is fairly flat, consider the recently added IO module, pandas.read_xml introduced in v1.3. Given XML uses a default namespace, to reference elements in xpath use namespaces argument:

url = "https://nvd.nist.gov/feeds/xml/cpe/dictionary/official-cpe-dictionary_v2.3.xml.zip"

df = pd.read_xml(
    url, xpath=".//doc:cpe-item", namespaces={'doc': 'http://cpe.mitre.org/dictionary/2.0'}
)

If you do not have the default parser, lxml, installed, use the etree parser:

df = pd.read_xml(
    url, xpath=".//doc:cpe-item", namespaces={'doc': 'http://cpe.mitre.org/dictionary/2.0'}, parser="etree"
)