Question

Xml flattener and multiple occurences of an element

M
Badge

Hi,

I use XML flattener like this to expose an element. What I want, and what this does, is make a column with the element name as its name, and put the value of the xml element that was matched as its value.

The problem is when "ElementToMatch" occurs multiple times in the XML file. It only makes one new column and puts the first occurring value that was matched inside. Is there a way to make as many columns as needed so that each found value ends up in the results? Order and empty values (if only one is found for example) don't matter.

Thanks a bunch in advance.

25 replies

takashi
Rank
Userlevel 2
Badge +17

In this kind of question, it's recommended to explain using a simplified example so that we can understand correctly what the requirement is.

If you need to extract the values of <attribute> elements from this XML, for example, what attributes (pairs of attribute name and value) should the output feature have?

<?xml version="1.0"?>
<root>
  <attribute>value1</attribute>
  <attribute>value2</attribute>
  <attribute>value3</attribute>
</root>
M
Badge

It should be 3 columns, each with the name 'attribute' and the values of value1, value2 and value3. But, in each xml file I process, 'attribute' can occur a different number of times. There need to be enough columns so each value can be written for a certain row. If some spots are empty in the result thats fine.

So this:

From:

xml1:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

</root>

xml2:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

<attribute>value3</attribute>

<attribute>value4</attribute>

</root>

xml3:

<root>

<attribute>value1</attribute>

</root>

takashi
Rank
Userlevel 2
Badge +17

It should be 3 columns, each with the name 'attribute' and the values of value1, value2 and value3. But, in each xml file I process, 'attribute' can occur a different number of times. There need to be enough columns so each value can be written for a certain row. If some spots are empty in the result thats fine.

So this:

From:

xml1:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

</root>

xml2:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

<attribute>value3</attribute>

<attribute>value4</attribute>

</root>

xml3:

<root>

<attribute>value1</attribute>

</root>

Every column (feature attribute) name has to be unique. You cannot add two or more "attribute" columns to the same feature (record).

 

However, you can create a list attribute called "attribute{}" (attribute{0} = value1, attribute{1} = value2, ...) if you set "root" (the parent element of the attribute elements) to the Elements to Match parameter.

 

 

takashi
Rank
Userlevel 2
Badge +17

It should be 3 columns, each with the name 'attribute' and the values of value1, value2 and value3. But, in each xml file I process, 'attribute' can occur a different number of times. There need to be enough columns so each value can be written for a certain row. If some spots are empty in the result thats fine.

So this:

From:

xml1:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

</root>

xml2:

<root>

<attribute>value1</attribute>

<attribute>value2</attribute>

<attribute>value3</attribute>

<attribute>value4</attribute>

</root>

xml3:

<root>

<attribute>value1</attribute>

</root>

Then, this Q&A; might help you: list flattening

 

 

M
Badge
Every column (feature attribute) name has to be unique. You cannot add two or more "attribute" columns to the same feature (record).

 

However, you can create a list attribute called "attribute{}" (attribute{0} = value1, attribute{1} = value2, ...) if you set "root" (the parent element of the attribute elements) to the Elements to Match parameter.

 

 

Thanks! I'm still new to FME: where do I define this list? or do I just write attribute{}?

 

 

takashi
Rank
Userlevel 2
Badge +17
Every column (feature attribute) name has to be unique. You cannot add two or more "attribute" columns to the same feature (record).

 

However, you can create a list attribute called "attribute{}" (attribute{0} = value1, attribute{1} = value2, ...) if you set "root" (the parent element of the attribute elements) to the Elements to Match parameter.

 

 

FME defines the list name "attribute{}" automatically based on the content of the source XML (i.e. from the element name <attribute>). You can just expose it via the Attributes to Expose parameter if necessary.

 

 

M
Badge
FME defines the list name "attribute{}" automatically based on the content of the source XML (i.e. from the element name <attribute>). You can just expose it via the Attributes to Expose parameter if necessary.

 

 

Ok so like this?

 

 

 

Inside "antwoord" there are a number of inp.bsn elements I want to extract. So this should do it?

 

takashi
Rank
Userlevel 2
Badge +17
FME defines the list name "attribute{}" automatically based on the content of the source XML (i.e. from the element name <attribute>). You can just expose it via the Attributes to Expose parameter if necessary.

 

 

I cannot see your XML that contains <antwoord> and <inp.bsn> elements, but if the <inp.bsn> elements are the children of the <antwoord> element, the same approach as the example above should work.

 

 

M
Badge
I cannot see your XML that contains <antwoord> and <inp.bsn> elements, but if the <inp.bsn> elements are the children of the <antwoord> element, the same approach as the example above should work.

 

 

Sorry I forgot the attachment! xml.png

 

 

takashi
Rank
Userlevel 2
Badge +17
FME defines the list name "attribute{}" automatically based on the content of the source XML (i.e. from the element name <attribute>). You can just expose it via the Attributes to Expose parameter if necessary.

 

 

If you want to get an answer corresponding to your actual XML schema, post a minimal and valid XML fragment. Just a fragment which contains the part of your interest is required. Do not post the full XML document.
M
Badge
If you want to get an answer corresponding to your actual XML schema, post a minimal and valid XML fragment. Just a fragment which contains the part of your interest is required. Do not post the full XML document.
xmlexample.xml

 

I need the <inp.bsn> values. Thanks by the way for helping me out!

 

M
Badge

I need the <inp.bsn> values. Thanks by the way for helping me out!

https://knowledge.safe.com/storage/attachments/15943-xmlexample.xml

takashi
Rank
Userlevel 2
Badge +17

Thanks for posting a good sample. It's small but enough explaining the schema of interest.

<?xml version="1.0"?>
<root>
  <antwoord>
	<inp.bsn>value1</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
	<inp.bsn>value2</inp.bsn>
	<inp.bsn>value3</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
  </antwoord>
</root>

The XMLFlattener with your setting definitely extracts values of all inp.bsn as elements of a list attribute called "inp.bsn{}".

Here, you have set "inp.bsn{0}" to the Attributes to Expose parameter. This setting only exposes the first element of the list. It's not wrong for syntax, but the result may not be your expected one.
 I think you need to expose multiple elements.
If so, set the list name "inp.bsn{}" (without element index) to the parameter, you can then expose your desired number of elements easily.

See here to lean more: Exposing List Attributes

After exposing, do what then? This may be an option: list flattening

0684Q00000ArLFmQAN.png

M
Badge

Thanks for posting a good sample. It's small but enough explaining the schema of interest.

<?xml version="1.0"?>
<root>
  <antwoord>
	<inp.bsn>value1</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
	<inp.bsn>value2</inp.bsn>
	<inp.bsn>value3</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
  </antwoord>
</root>

The XMLFlattener with your setting definitely extracts values of all inp.bsn as elements of a list attribute called "inp.bsn{}".

Here, you have set "inp.bsn{0}" to the Attributes to Expose parameter. This setting only exposes the first element of the list. It's not wrong for syntax, but the result may not be your expected one.
 I think you need to expose multiple elements.
If so, set the list name "inp.bsn{}" (without element index) to the parameter, you can then expose your desired number of elements easily.

See here to lean more: Exposing List Attributes

After exposing, do what then? This may be an option: list flattening

0684Q00000ArLFmQAN.png

I currently have it set like this (see attachment) transformersettings.png, but all the attributes values are missing (there are at least 4 inp.bsn attributes in the xml.result.png

 

 

takashi
Rank
Userlevel 2
Badge +17

Thanks for posting a good sample. It's small but enough explaining the schema of interest.

<?xml version="1.0"?>
<root>
  <antwoord>
	<inp.bsn>value1</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
	<inp.bsn>value2</inp.bsn>
	<inp.bsn>value3</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
  </antwoord>
</root>

The XMLFlattener with your setting definitely extracts values of all inp.bsn as elements of a list attribute called "inp.bsn{}".

Here, you have set "inp.bsn{0}" to the Attributes to Expose parameter. This setting only exposes the first element of the list. It's not wrong for syntax, but the result may not be your expected one.
 I think you need to expose multiple elements.
If so, set the list name "inp.bsn{}" (without element index) to the parameter, you can then expose your desired number of elements easily.

See here to lean more: Exposing List Attributes

After exposing, do what then? This may be an option: list flattening

0684Q00000ArLFmQAN.png

Why not set antwoord (parent of <inp.bsn> elements) to the Elements to Match parameter? This is your previous screenshot.

 

0684Q00000ArMj1QAF.png

M
Badge
Why not set antwoord (parent of <inp.bsn> elements) to the Elements to Match parameter? This is your previous screenshot.

 

You're right, I did, same result...

 

takashi
Rank
Userlevel 2
Badge +17
Why not set antwoord (parent of <inp.bsn> elements) to the Elements to Match parameter? This is your previous screenshot.

 

Once run the workspace, select the feature on the Table View in FME Data Inspector, and check details of the feature with the Feature Information window.

 

 

takashi
Rank
Userlevel 2
Badge +17

Thanks for posting a good sample. It's small but enough explaining the schema of interest.

<?xml version="1.0"?>
<root>
  <antwoord>
	<inp.bsn>value1</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
	<inp.bsn>value2</inp.bsn>
	<inp.bsn>value3</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
  </antwoord>
</root>

The XMLFlattener with your setting definitely extracts values of all inp.bsn as elements of a list attribute called "inp.bsn{}".

Here, you have set "inp.bsn{0}" to the Attributes to Expose parameter. This setting only exposes the first element of the list. It's not wrong for syntax, but the result may not be your expected one.
 I think you need to expose multiple elements.
If so, set the list name "inp.bsn{}" (without element index) to the parameter, you can then expose your desired number of elements easily.

See here to lean more: Exposing List Attributes

After exposing, do what then? This may be an option: list flattening

0684Q00000ArLFmQAN.png

Ah, you encountered the case where the number of <inp.bsn> elements is just one.

 

In the case, the XML flattening functionality in FME works to extract the element as a regular attribute (not list) by default.

 

If you need to extract <inp.bsn> elements always as list elements regardless of the number of them, you will have to edit the Flatten Options with Advanced mode.

 

Open the Flattnen Options dialog with Advanced mode.

 

0684Q00000ArMiEQAV.png

 

Then, edit the cardinality attribute.

 

cardinality="*/inp.bsn{}/+
+{?}"

 

0684Q00000ArMj6QAF.png

 

See here to learn more about the cardinality attribute: Structure Element | xfMap

 

 

 

M
Badge
Ah, you encountered the case where the number of <inp.bsn> elements is just one.

 

In the case, the XML flattening functionality in FME works to extract the element as a regular attribute (not list) by default.

 

If you need to extract <inp.bsn> elements always as list elements regardless of the number of them, you will have to edit the Flatten Options with Advanced mode.

 

Open the Flattnen Options dialog with Advanced mode.

 

0684Q00000ArMiEQAV.png

 

Then, edit the cardinality attribute.

 

cardinality="*/inp.bsn{}/+
+{?}"

 

0684Q00000ArMj6QAF.png

 

See here to learn more about the cardinality attribute: Structure Element | xfMap

 

 

 

Ok, now I get the first value only. But I know realize the other elements are nested even further down, inside an element called "gerelateerde".. So it looks like this: xmlexample.xml

 

 

Is it possible to extract all 3 values? 

 

Thanks so much for helping me out, you're the best. 

 

takashi
Rank
Userlevel 2
Badge +17

Thanks for posting a good sample. It's small but enough explaining the schema of interest.

<?xml version="1.0"?>
<root>
  <antwoord>
	<inp.bsn>value1</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
	<inp.bsn>value2</inp.bsn>
	<inp.bsn>value3</inp.bsn>
	<someotherstuff>noOfInterest</someotherstuff>
  </antwoord>
</root>

The XMLFlattener with your setting definitely extracts values of all inp.bsn as elements of a list attribute called "inp.bsn{}".

Here, you have set "inp.bsn{0}" to the Attributes to Expose parameter. This setting only exposes the first element of the list. It's not wrong for syntax, but the result may not be your expected one.
 I think you need to expose multiple elements.
If so, set the list name "inp.bsn{}" (without element index) to the parameter, you can then expose your desired number of elements easily.

See here to lean more: Exposing List Attributes

After exposing, do what then? This may be an option: list flattening

0684Q00000ArLFmQAN.png

In that case, the XMLXQueryExtrctor may be easier.

 

XQuery Expression:

 

//inp.bsn/text()

 

0684Q00000ArMjBQAV.png

 

M
Badge
In that case, the XMLXQueryExtrctor may be easier.

 

XQuery Expression:

 

//inp.bsn/text()

 

0684Q00000ArMjBQAV.png

 

I've tried this, it doesn't work :( I've tried tweaking all sorts of parameters, I don't know why it doesn't work 

 

 

takashi
Rank
Userlevel 2
Badge +17
In that case, the XMLXQueryExtrctor may be easier.

 

XQuery Expression:

 

//inp.bsn/text()

 

0684Q00000ArMjBQAV.png

 

This works just fine for me: xmlxqueryextractor-example.fmw (FME 2017.1.2.1)

 

 

M
Badge
This works just fine for me: xmlxqueryextractor-example.fmw (FME 2017.1.2.1)

 

 

I see, that example works for me too. This is my setup:settings.png

 

 

I only get empty results. Maybe the namespacing has some effect? I've included an actual XML that gets parsed. A lot of stuff that is personal information has been taken out or replaced, but the structure and namespaces are the same. xml-example.xml

 

takashi
Rank
Userlevel 2
Badge +17
In that case, the XMLXQueryExtrctor may be easier.

 

XQuery Expression:

 

//inp.bsn/text()

 

0684Q00000ArMjBQAV.png

 

Yes, the information on namespaces in the source XML document is required, especially when you use an XQuery expression. In this case, "inp.bsn" elements have no namespace qualifier, but its ancestor element "npsLa01" has this default namespace definition:

 

xmlns="http://www.egem.nl/StUF/sector/bg/0310"
Therefore, you have to declare the namespace in the XQuery expression correctly like this.

 

declare default element namespace "http://www.egem.nl/StUF/sector/bg/0310";
//inp.bsn/text()

 

M
Badge
Yes, the information on namespaces in the source XML document is required, especially when you use an XQuery expression. In this case, "inp.bsn" elements have no namespace qualifier, but its ancestor element "npsLa01" has this default namespace definition:

 

xmlns="http://www.egem.nl/StUF/sector/bg/0310"
Therefore, you have to declare the namespace in the XQuery expression correctly like this.

 

declare default element namespace "http://www.egem.nl/StUF/sector/bg/0310";
//inp.bsn/text()

 

 

Thanks so much! That worked. Again, thank you for all your help these last few days. 

 

Reply


Community Stats

28,724
Posts
109,518
Replies
36,563
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.