Question

Combine XML and SQLCreator to process records

sunsilk11
Rank
Badge +6

@takashi

Hi I am using an SQLCreator to return all incidents reported in the last hour. I then want to use an XML file to define levels for each alarmtype, which determine whether an alert record is created depending on the type of incident and count e.g.

<incidentype name="gasleak" level="1,5,10"/>

<incidenttype name="electricityinterruption" level="1,3,10"/>

 

therefore for gasleak, an alarm record is created if count of incidents is as follows:

count alarmcreated Level AlarmTime

2 Yes 1

4 No N/A

7 Yes 5

11 Yes 10

Note when count=4, no alarm is created as level 1 alarm has already been created.

So far, I have added an XML reader and use FeaturePaths. I have also obtained the count of incidents for the last hour using SQLCreator.

Any suggestion how FeaturePaths could be used in this case? Is pythonXML the best way forward?

 

9 replies

takashi
Rank
Userlevel 2
Badge +17

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

sunsilk11
Rank
Badge +6

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

The FME script is run every 5 mins. I can get the count of incidents and other details from SQLcreator. If count of gas leaks in the last hour>=1, then an alarm record is created and email sent to specified address. The next alarm is created when count >=5, and likewise a third alarm gets created when count of gas leaks>=10 and emails/text messages sent where specified.

The complete xml file may contain other attributes like email, phone number (see attached). Hope this clarifies the rules.0684Q00000ArMYJQA3.png

takashi
Rank
Userlevel 2
Badge +17

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

Sorry I'm not sure what the point of your question is. Are you looking for a way to extract incident name and count number for each incident from the XML document?

sunsilk11
Rank
Badge +6

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

Yes, basically linking the result of sqlcreator with the xml to work out when an alarm record needs creating. 

takashi
Rank
Userlevel 2
Badge +17

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

I think you can use the XML reader with Feature Paths configuration. If you set "incidenttype" to the Elements to Match, the reader reads each <incidenttype> as a feature that will have these attributes.

  • incidenttype.name
  • level{}.phone
  • level{}.email
  • level{}.count

You can then explode the "level" list with the ListExploder, if necessary. 

sunsilk11
Rank
Badge +6

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

@takashi. Hi I have now refined my xml file. (see attached)

I am basically looking to process as follows:

Loop through incident types. For each levels, get the count and the email addresses, send an email and move to next email address etc.

When I use a listExploder setting the List Attribute parameter as Level{}, in the output xml_fragment attribute contains all the levels instead of 1 level.

I was hoping I could build the lists and then use python script to loop through the nested loops.

takashi
Rank
Userlevel 2
Badge +17

@takashi. Hi I have now refined my xml file. (see attached)

I am basically looking to process as follows:

Loop through incident types. For each levels, get the count and the email addresses, send an email and move to next email address etc.

When I use a listExploder setting the List Attribute parameter as Level{}, in the output xml_fragment attribute contains all the levels instead of 1 level.

I was hoping I could build the lists and then use python script to loop through the nested loops.

Cannot find the attachment. Please post the XML file. It would be better if you post a raw XML file rather than its screenshot.

sunsilk11
Rank
Badge +6

If the entire source XML document looks like this,

<incidenttypes>
    <incidenttype name="gasleak" level="1,5,10"/>
    <incidenttype name="electricityinterruption" level="1,3,10"/>
</incidenttypes>

the XML reader with Feature Paths (Elements to Match: incidenttypes/incidenttype) outputs features each of which holds these two attributes.

incidenttype.name
incidenttype.level

I think you can then use the features to determine the incident type of records from the SQLExecutor, but I'm unclear the rules for the determination. Could you please explain the rules bit more clearly?

Please find xml file attached. Thanks

I think I can get it to work by using 2 ListExploder List Attribute: Level{} and emails.email{}, then get the values using a pythoncaller as

feature.getAttribute('incidentype.name')

feature.getAttribute('count')

feature.getAttribute('address')

Is there a better way?

testconfig.xml

takashi
Rank
Userlevel 2
Badge +17

Basically you can read the XML document with the XML reader with feature paths configuration (Elements to Match: incidenttype).

<?xml version="1.0" encoding="ISO-8859-1"?>
<incidenttypes>
    <incidenttype name="gasleak">
        <Level count="2">
            <emails>
                <email address="a@b.com"/>    
            </emails>
        </Level>
    </incidenttype>
    <incidenttype name="electricityinterruption">
        <Level count="3">
            <emails>
                <email address="m@n.com"/>
                <email address="a@b.com"/>
            </emails>
        </Level>
        <Level count="5">
            <emails>
                <email address="a@c.com"/>
                <email address="f@g.com"/>
            </emails>
        </Level>
        <Level count="10">
            <emails>
                <email address="p@q.com"/>
                <email address="x@y.com"/>
            </emails>
        </Level>
    </incidenttype>
</incidenttypes>

However, the XML reader reads a single child element as a regular attribute and multiple child elements as a list attribute by default. Regular attribute and list attribute are mixed for the same element could make complex the subsequent processes.

If you want to populate all the child elements into a list attribute regardless of the number of them, you can define explicitly which elements should be list with the Flatten Options (Advanced mode - cardinality attribute), as in:

cardinality="*/Level{}/emails/email{}
*/Level{}
+{?}"

0684Q00000ArKR4QAN.png

See here to learn more about the <structure> configuration: Structure Element | xfMap

Reply


Community Stats

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