Skip to main content
  • EN

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>

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 @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.

 

 

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

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?

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.

 

 

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!
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

 

 

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?
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?

 

 

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/groupf@name = 'HR_SHARED_GIVEN_NAMES']/field
return fme:set-attribute($f/@name||$i, data($f)),
for $f in /row/groupa@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 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


Community Stats

30,108
Posts
114,295
Replies
37,743
Members
Terms & ConditionsCookie 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.