XML With labeled metadata

Highlighted
Six Stars

XML With labeled metadata

I have XML that I have retrieved from an API that looks something like this

<dataset
    xmlns="http://developer.cognos.com/schemas/xmldata/1/"
    xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
    xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd"
>
-->
    <metadata>
          <item name="Column1" type="xs:string" length="12"/>
          <item name="Column2" type="xs:string" length="84"/>
          <item name="Column3" type="xs:string" length="6"/>
          <item name="Column4" type="xs:string" length="14"/>
          <item name="Column5" type="xs:string" length="20"/>
          <item name="Column6" type="xs:string" length="2414"/>
          <item name="Column7" type="xs:dateTime"/>
          <item name="Column8" type="xs:decimal" scale="6" precision="16"/>
    </metadata>
    <data>
        <row>
            <value>word  </value>
            <value> words</value>
            <value>words</value>
            <value>88254  </value>
            <value>50011   </value>
            <value>words</value>
            <value>2019-10-23T00:00:00</value>
            <value>10.51</value>
        </row>
        <row>
            <value>words  </value>
            <value> words</value>
            <value>words</value>
            <value>88254  </value>
            <value>50060   </value>
            <value>words</value>
            <value>2019-10-23T00:00:00</value>
            <value>20.96</value>
        </row>
    </data>
</dataset>

The differences between my real data and this anonymized sample is that my real data has additional rows and columns. How do map this XML document to a csv with the metadata set as shown in the XML? Currently the whole document is in one talend "row". I have tried to use tExtractXMLField, but can only get the first value in each row. I can't figure out how to get the schema to map from the XML itself.


Accepted Solutions
Community Manager

Re: XML With labeled metadata

The way you need to do this can be seen in the example job below....

 

Screenshot 2019-10-28 at 10.07.33.png

 

The configuration of the tFileInputXML is as below....

Screenshot 2019-10-28 at 10.12.06.png
The tExtractXMLField component is configured as below.....

Screenshot 2019-10-28 at 10.16.11.png

Essentially you are looping on /dataset/data/row and to retrieve each of the value columns you use the syntax "./value[{number of row}]"

View solution in original post


All Replies
Community Manager

Re: XML With labeled metadata

The way you need to do this can be seen in the example job below....

 

Screenshot 2019-10-28 at 10.07.33.png

 

The configuration of the tFileInputXML is as below....

Screenshot 2019-10-28 at 10.12.06.png
The tExtractXMLField component is configured as below.....

Screenshot 2019-10-28 at 10.16.11.png

Essentially you are looping on /dataset/data/row and to retrieve each of the value columns you use the syntax "./value[{number of row}]"

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog