Follow

Connect to XML with a CData data source

Before you attempt this you need to add the XML provider to your list of available sources.

Go through this guide before you start.

Add a CData data source

XML Information

XML files can be structured in different ways

Here below is how a XML file structured in element form looks:

<moviesbyearnings>
<movie>
<yr>2006</yr>
<rank>1</rank>
<tit>Pirates of the Caribbean: Dead Man's Chest</tit>
<grs>423315812.000000</grs>
<opn>2015-07</opn>
<cnt>4133</cnt>
  </movie>
  <movie>  
<yr>2006</yr>
<rank>2</rank>
<tit>Night at the Museum</tit>
<grs>250863268.000000</grs>
<opn>2015-12</opn>
<cnt>3768</cnt>
  </movie>
</moviesbyearnings>

Here is how a XML file structured in attribute form looks:

<w>
   <worldheritagesites Name="Aachen Cathedral" Year="1978" CountryName="Germany" CountryCode="DE" />
   <worldheritagesites Name="Aapravasi Ghat" Year="2006" CountryName="Mauritius" CountryCode="MU" />
   <worldheritagesites Name="Abbey and Altenmünster of Lorsch" Year="1991" CountryName="Germany" CountryCode="DE" />
   <worldheritagesites Name="Abu Mena" Year="1979" CountryName="Egypt" CountryCode="EG" />
   <worldheritagesites Name="Aeolian Islands" Year="2000" CountryName="Italy" CountryCode="IT" />
   <worldheritagesites Name="Aflaj Irrigation System of Oman" Year="2006" CountryName="Oman" CountryCode="OM" />
   <worldheritagesites Name="Agave Landscape and Ancient Industrial Facilities of Tequila" Year="2006" CountryName="Mexico" CountryCode="MX" />  
<worldheritagesites Name="Agra Fort" Year="1983" CountryName="India" CountryCode="IN" />   <worldheritagesites Name="Agricultural Landscape of Southern Oland" Year="2000" CountryName="Sweden" CountryCode="SE" /> <worldheritagesites Name="Air and Tenere Natural Reserves" Year="1991" CountryName="Niger" CountryCode="NE" /> <worldheritagesites Name="Ajanta Caves" Year="1983" CountryName="India" CountryCode="IN" /> <worldheritagesites Name="Aksum" Year="1980" CountryName="Ethiopia" CountryCode="ET" /> <worldheritagesites Name="Al Qal'a of Beni Hammad" Year="1980" CountryName="Algeria" CountryCode="DZ" />
</w>

To make the Kings.XML file Download this link and save it as Kings.XML

http://mysafeinfo.com/api/data?list=englishmonarchs&mappingtype=element&rootname=englishmonarchs&elementname=Monarch

This is the URI link to the file I use for the element setup.

http://mysafeinfo.com/api/data?list=moviesbyearnings2006&mappingtype=element&rootname=moviesbyearnings&elementname=movie

This is the URI link to the file that uses attributes.

http://mysafeinfo.com/api/data?list=worldheritagesites&format=xml&rootname=w&elementname=worldheritagesites&alias=nm=Name,yr=Year,cn=CountryName,cc=CountryCode

CData Setup

Add a CData data source and choose the XML provider.

The first step in setting up a CData connector should always be to click the ? in the top right corner.

5.PNG

XML guide from CData

With Elements

If the XML source is a file you need to fill out the following fields.

Data Source: <the location of the kings.xml file>

Just the link to the file. Remember that all users that executes the project must have access to this folder.

XPath: englishmonarchs/Monarch

XPath is the path to the data. It is the rootname followed by the element name. It is a good idea to locate this beforehand.

5.PNG

If your XML is stored in an online location, you need to fill out the following fields.

URI: http://mysafeinfo.com/api/data?list=moviesbyearnings2006&mappingtype=element&rootname=moviesbyear&elementname=Movie

It stands for Uniform Resource Provider and it is the resource location. You need to paste in the link to the file.

XPath: moviesbyear/Movie

Similar to before you need the rootname followed by the element name.

1.PNG

With Attributes

If the XML file is using attributes as its element type, there is something you need to take into account. It creates a _ in front of the table names.

This means that with attributes it is _yr and with elements yr.

Other than that it is pretty much the same.

URI: http://mysafeinfo.com/api/data?list=worldheritagesites&format=xml&rootname=w&elementname=worldheritagesites&alias=nm=Name,yr=Year,cn=CountryName,cc=CountryCode

Again you need to add a link to the file.

XPath: w/worldheritagesites

Similar to before you need the rootname followed by the element name.

Backwards Compatibility Mode: True

The difference is that you need to set Backwards Compatibility Mode to True. Otherwise it wont recognize the fields.

2.PNG

Getting the data

Following the setup and successful connection test, you need to synchronize to the data source and preview the data.

If the file is set up with elements, it looks like this.

3.PNG

If it uses attributes, it looks like this. Remember to remove the blank field.

4.PNG

After this is done you can add additional connections. You just need to add files with the same metadata.

Additional_Data_source.PNG

The way I set it up is that I used a moviesbyearning file. The second link, which has data for 2005. You can then add the subsequent years as additional data sources. The only thing you need to take into account is that the XPath needs to be similar in each file.

8.PNG

Besides this you can also set up Query Tables, Data Type overwrites, Incremental load and data selection rules.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.