Skip to main content
Solved

How to convert xml to Excel table

K
Forum|alt.badge.img

Hi,

new here. Just want to ask how do I extract data from xml below into an Excel Table. Problem I specifically can't get over with is how to make the attribute name into a column heading in Excel and how to extract from inside the groups.

Cheers.

<?xml version="1.0" encoding="windows-1252" ?>

 

<file name="EMP_RECORD" version="3.0.0">

 

<schema>

 

<group name="EMP_RECORD_HOURS_DAY_DATE">

 

<field name="HOURS_DAY_DATE">

 

<desc>Effective Date</desc>

 

<just>R</just>

 

<len>12</len>

 

<type>DATE</type>

 

</field>

 

<field name="HOURS_DAY_CODE">

 

<desc>Hours/Days Register</desc>

 

<just>L</just>

 

<len>2</len>

 

<type>VARCHAR</type>

 

</field>

 

<field name="PAY_GROUP">

 

<desc>PAY_GROUP</desc>

 

<just>L</just>

 

<len>20</len>

 

<type>VARCHAR</type>

 

<foreign_key>

 

<target_file>PAY_GROUPS</target_file>

 

<key_parts>PAY_GROUP</key_parts>

 

</foreign_key>

 

</field>

 

<field name="WORK_PATTERN_CODE">

 

<desc>WORK_PATTERN_CODE</desc>

 

<just>L</just>

 

<len>20</len>

 

<type>VARCHAR</type>

 

</field>

 

<field name="WORK_PATTERN_WEEK">

 

<desc>WORK_PATTERN_WEEK</desc>

 

<just>R</just>

 

<len>14</len>

 

<type>INTEGER</type>

 

</field>

 

</group>

 

<field name="SURNAME">

 

<desc>Surname</desc>

 

<just>L</just>

 

<len>20</len>

 

<type>VARCHAR</type>

 

</field>

 

<field name="TITLE">

 

<desc>Title</desc>

 

<just>L</just>

 

<len>4</len>

 

<type></type>

 

</field>

 

<field name="FIRST_NAME">

 

<desc>First Name</desc>

 

<just>L</just>

 

<len>20</len>

 

<type></type>

 

</field>

 

<field name="PREF_NAME">

 

<desc>Preferred Name</desc>

 

<just>L</just>

 

<len>15</len>

 

<type>VARCHAR</type>

 

</field>

 

</schema>

 

<data>

 

<row key="Emp1">

 

<group name="EMP_RECORD_HOURS_DAY_DATE">

 

<field name="HOURS_DAY_DATE">09 JAN 2001</field>

 

<field name="HOURS_DAY_CODE">14</field>

 

</group>

 

<field name="SURNAME">DOE</field>

 

<field name="TITLE">MS</field>

 

<field name="FIRST_NAME">JANE</field>

 

<field name="PREF_NAME">JANE</field>

 

</row>

 

<row key="Emp2">

 

<group name="EMP_RECORD_HOURS_DAY_DATE">

 

<field name="HOURS_DAY_DATE">19 JAN 2001</field>

 

<field name="HOURS_DAY_CODE">14</field>

 

</group>

 

<field name="SURNAME">SMITH</field>

 

<field name="TITLE">MR</field>

 

<field name="FIRST_NAME">JOHN</field>

 

<field name="PREF_NAME">JOHN</field>

 

</row>

 

</data>

 

</file>

Best answer by takashi

If I understood the requirement correctly, the XMLFragmenter and the XMLXQueryExtractor might help you.

XQeury Expression Example:

for $f in //field
return fme:set-attribute($f/@name, data($f))

0684Q00000ArJLJQA3.png

View original
1 Attachments
Did this help you find an answer to your question?

11 replies

G
Forum|alt.badge.img
  • guthima
  • July 24, 2018

Hi @kburata,

You may try using the XML reader and Feature Paths to select the attributes that you're interested in.

Result would look something like this:

K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
guthima wrote:

Hi @kburata,

You may try using the XML reader and Feature Paths to select the attributes that you're interested in.

Result would look something like this:

Hi @guthima, ideally I would be able to get data out of "data" part and concert it into a table.

 

 

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • July 25, 2018

If I understood the requirement correctly, the XMLFragmenter and the XMLXQueryExtractor might help you.

XQeury Expression Example:

for $f in //field
return fme:set-attribute($f/@name, data($f))

0684Q00000ArJLJQA3.png

K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
takashi wrote:

If I understood the requirement correctly, the XMLFragmenter and the XMLXQueryExtractor might help you.

XQeury Expression Example:

for $f in //field
return fme:set-attribute($f/@name, data($f))

0684Q00000ArJLJQA3.png

Hi @takashi, how do I expose row.key though?
takashi
Influencer
  • takashiInfluencer
  • July 25, 2018
takashi wrote:

If I understood the requirement correctly, the XMLFragmenter and the XMLXQueryExtractor might help you.

XQeury Expression Example:

for $f in //field
return fme:set-attribute($f/@name, data($f))

0684Q00000ArJLJQA3.png

Modify the XQuery expression as:

 

fme:set-attribute('ROW_KEY', data(/row/@key)),
for $f in //field
return fme:set-attribute($f/@name, data($f))
and expose "ROW_KEY" additionally via the Attributes to Expose parameter.

 

 

K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
takashi wrote:
Modify the XQuery expression as:

 

fme:set-attribute('ROW_KEY', data(/row/@key)),
for $f in //field
return fme:set-attribute($f/@name, data($f))
and expose "ROW_KEY" additionally via the Attributes to Expose parameter.

 

 

 

Awesome mate. cheers!
K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
takashi wrote:
Modify the XQuery expression as:

 

fme:set-attribute('ROW_KEY', data(/row/@key)),
for $f in //field
return fme:set-attribute($f/@name, data($f))
and expose "ROW_KEY" additionally via the Attributes to Expose parameter.

 

 

Hi @takashi,

 

If my xml was changed to the one below, how would I change the xquery to set the attribute of the 2nd given names to MIDDLE_NAME or split them into GIVEN_NAME1 and GIVEN_NAME2.

 

 

0684Q00000ArN3fQAF.png

 

I have this right now but having an error setting the variable's value to check if still within the same ROWKEY as I cycle through the fields. Is this possible?

 

 

0684Q00000ArN3pQAF.png

 

 

Cheers

 

 

takashi
Influencer
  • takashiInfluencer
  • July 25, 2018
kburata wrote:
Hi @takashi,

 

If my xml was changed to the one below, how would I change the xquery to set the attribute of the 2nd given names to MIDDLE_NAME or split them into GIVEN_NAME1 and GIVEN_NAME2.

 

 

 

I have this right now but having an error setting the variable's value to check if still within the same ROWKEY as I cycle through the fields. Is this possible?

 

 

 

 

Cheers

 

 

In your example, both "JANE" and "BEVERLY" belong to the same field "GIVEN_NAMES", whereas FME feature can only have a single value for each attribute. The solution would be different depending on your desired result. Which one?

 

  1. Create two attributes. e.g. GIVEN_NAMES1 = JANE, GIVEN_NAMES2 = BEVERLY.
  2. Create a list attribute containing the two values. e.g. GIVEN_NAME{0} = JANE, GIVEN_NAMES{1} = BEVERLY.
  3. Other?
K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
takashi wrote:
In your example, both "JANE" and "BEVERLY" belong to the same field "GIVEN_NAMES", whereas FME feature can only have a single value for each attribute. The solution would be different depending on your desired result. Which one?

 

  1. Create two attributes. e.g. GIVEN_NAMES1 = JANE, GIVEN_NAMES2 = BEVERLY.
  2. Create a list attribute containing the two values. e.g. GIVEN_NAME{0} = JANE, GIVEN_NAMES{1} = BEVERLY.
  3. Other?
The 1st one?

 

 

takashi
Influencer
  • takashiInfluencer
  • July 25, 2018
takashi wrote:

If I understood the requirement correctly, the XMLFragmenter and the XMLXQueryExtractor might help you.

XQeury Expression Example:

for $f in //field
return fme:set-attribute($f/@name, data($f))

0684Q00000ArJLJQA3.png

This is a possible expression.

 

fme:set-attribute('ROW_KEY', data(/row/@key)),
for $f at $i in /row/group[@name = 'HR_SHARED_GIVEN_NAMES']/field
return fme:set-attribute($f/@name||$i, data($f)),
for $f in /row/group[@name != 'HR_SHARED_GIVEN_NAMES']/field
return fme:set-attribute($f/@name, data($f)),
for $f in /row/field
return fme:set-attribute($f/@name, data($f))

 

K
Forum|alt.badge.img
  • kburata
  • Author
  • July 25, 2018
takashi wrote:
This is a possible expression.

 

fme:set-attribute('ROW_KEY', data(/row/@key)),
for $f at $i in /row/group[@name = 'HR_SHARED_GIVEN_NAMES']/field
return fme:set-attribute($f/@name||$i, data($f)),
for $f in /row/group[@name != 'HR_SHARED_GIVEN_NAMES']/field
return fme:set-attribute($f/@name, data($f)),
for $f in /row/field
return fme:set-attribute($f/@name, data($f))

 

This is awesome! Thanks a lot.

 

Will have to brush up on my xquery skills.

 

 

Reply


Helpful Members This Week

Community Stats

31,135
Posts
118,046
Replies
38,777
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.