XML to CSV with xmlstarlet

Question

I got some OAI-PMH XML files that is formatted as like below:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="/bundles/ojsoai/oai2.xsl" ?>
<OAI-PMH xmlns="http://www.openarchives.org/OAI/2.0/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.openarchives.org/OAI/2.0/          http://www.openarchives.org/OAI/2.0/OAI-PMH.xsd">
  <responseDate>2015-12-26T23:54:31Z</responseDate>
  <request verb="ListRecords" metadataPrefix="oai_dc">http://oai_dc/</request>
  <ListRecords>
    <record>
      <header>
        <identifier>identifier</identifier>
        <datestamp>2015-12-01T00:00:00Z</datestamp>
        <setSpec>iksad</setSpec>
      </header>
      <metadata>
        <oai_dc:dc xmlns:oai_dc="http://www.openarchives.org/OAI/2.0/oai_dc/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.openarchives.org/OAI/2.0/oai_dc/      http://www.openarchives.org/OAI/2.0/oai_dc.xsd">
          <dc:title xml:lang="en-US">title en</dc:title>
          <dc:title xml:lang="fr-FR">title fr</dc:title>
          <dc:creator>creator</dc:creator>
          <dc:subject>subject</dc:subject>
          <dc:description>description1</dc:description>
          <dc:description>description2</dc:description>
          <dc:publisher>publisher</dc:publisher>
          <dc:date>2015-12-01T00:00:00Z</dc:date>
          <dc:type>type</dc:type>
          <dc:format>application/pdf</dc:format>
          <dc:identifier>identifier</dc:identifier>
          <dc:identifier/>
          <dc:source xml:lang="en-US">source</dc:source>
          <dc:source>source</dc:source>
          <dc:source>source</dc:source>
          <dc:language>en</dc:language>
          <dc:relation>relation</dc:relation>
        </oai_dc:dc>
      </metadata>
    </record>
    <record>
      <header>
      ..
      ..

I tried

xml sel -T -t -m '/OAI-PMH/ListRecords/record/metadata' test.xml

and

xml sel -t -v "//*[local-name()='metadata']" test.xml

I just want to convert CSV file formatted as but no luck.

title lang us; title lang fr; description1; description2

Can anyone help me?

Thank you in advance for your help...

Answer 1

There's a lot of namespaces here. There's 100% sure a better way to do this (xmlstarlet has namespaces support) but this should work :

xml sel -T -t -m //*[name()='record'] -v "concat(*[contains(name(),'metadata')]/*/*[contains(name(),'title')][1],';',*[contains(name(),'metadata')]/*/*[contains(name(),'title')][2],';',*[contains(name(),'metadata')]/*/*[contains(name(),'description')][1],';',*[contains(name(),'metadata')]/*/*[contains(name(),'description')][2])" -n data.xml >toto.csv

Output : toto.csv

title en;title fr;description1;description2

I've fixed your sample XML to test with xmlstarlet. data.xml :

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="/bundles/ojsoai/oai2.xsl" ?>
<OAI-PMH xmlns="http://www.openarchives.org/OAI/2.0/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.openarchives.org/OAI/2.0/          http://www.openarchives.org/OAI/2.0/OAI-PMH.xsd">
  <responseDate>2015-12-26T23:54:31Z</responseDate>
  <request verb="ListRecords" metadataPrefix="oai_dc">http://oai_dc/</request>
  <ListRecords>
    <record>
      <header>
        <identifier>identifier</identifier>
        <datestamp>2015-12-01T00:00:00Z</datestamp>
        <setSpec>iksad</setSpec>
      </header>
      <metadata>
        <oai_dc:dc xmlns:oai_dc="http://www.openarchives.org/OAI/2.0/oai_dc/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.openarchives.org/OAI/2.0/oai_dc/      http://www.openarchives.org/OAI/2.0/oai_dc.xsd">
          <dc:title xml:lang="en-US">title en</dc:title>
          <dc:title xml:lang="fr-FR">title fr</dc:title>
          <dc:creator>creator</dc:creator>
          <dc:subject>subject</dc:subject>
          <dc:description>description1</dc:description>
          <dc:description>description2</dc:description>
          <dc:publisher>publisher</dc:publisher>
          <dc:date>2015-12-01T00:00:00Z</dc:date>
          <dc:type>type</dc:type>
          <dc:format>application/pdf</dc:format>
          <dc:identifier>identifier</dc:identifier>
          <dc:identifier/>
          <dc:source xml:lang="en-US">source</dc:source>
          <dc:source>source</dc:source>
          <dc:source>source</dc:source>
          <dc:language>en</dc:language>
          <dc:relation>relation</dc:relation>
        </oai_dc:dc>
      </metadata>
    </record>
</ListRecords>
</OAI-PMH>

EDIT : More elegant command line :

xml sel -N x="http://purl.org/dc/elements/1.1/" -t -m "//_:record" -v "concat(_:metadata/*/x:title[@xml:lang='en-US'],';',_:metadata/*/x:title[@xml:lang='fr-FR'],';',_:metadata/*/x:description[1],';',_:metadata/*/x:description[2])" -n data.xml >toto.csv