cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jja
Post Prodigy
Post Prodigy

process XML with parameters

Hi i have folowinf XML. I don't need info from header i want to be able to extract and create HTML tabl;e for each field id="abonento-numeris" and populate with certain field values

 

lest say field abonento-numeris,abonentinis-mokestis(from eilutes-tipas "D"), kiti-mokesciai(from eilutes-tipas "S")

I tried nonverting it to JSON and then parse JSON but it does not return me my values.

How to work with such XML structure correctly?

 

<?xml version="1.0" encoding="UTF-8"?>
<documents>
<document id="konsoliduota-saskaita">
<header>
<parameter id="kliento-pavadinimas"> "Company"</parameter>
<parameter id="adresas1">Adress1</parameter>
<parameter id="adresas2">Adress2</parameter>
<parameter id="adresas3">Adress3</parameter>
<parameter id="adresas4" />
<parameter id="periodas">2022 09 01-2022 09 30</parameter>
<parameter id="kliento-numeris">393134</parameter>
<parameter id="dokumento-numeris">227651233</parameter>
<parameter id="dokumento-data">2022 09 30</parameter>
<parameter id="kredito-limitas">0000.00</parameter>
<parameter id="mokejimo-terminas">2022 10 31</parameter>
<parameter id="imones-kodas">11111111</parameter>
<parameter id="pvm-moketojo-kodas">111111114</parameter>
<parameter id="viso-uz-paslaugas-be-pvm">999.99</parameter>
<parameter id="viso-suma-be-pvm">666.66</parameter>
<parameter id="viso-suma-pvm">123.12</parameter>
<parameter id="viso-suma-su-pvm">1111.11</parameter>
</header>
<details>
<record>
<field id="abonento-tipas">GSM ryšio abonentai</field>
<field id="abonento-numeris">66666666</field>
<field id="abonento-vardas" />
<field id="mokejimo-planas">Verslas 0</field>
<field id="abonentinis-mokestis">0.00</field>
<field id="kiti-mokesciai">0.00</field>
<field id="vietiniai-balso-skambuciai">0.00</field>
<field id="vietiniai-sms">0.00</field>
<field id="vietinis-duomenu-perdavimas">0.00</field>
<field id="tarptautiniai-skambuciai">0.00</field>
<field id="tarptinkliniai-skambuciai">0.00</field>
<field id="kiti-papildomos-paslaugos">0.00</field>
<field id="nuolaida">0.00</field>
<field id="suma-be-pvm">0.00</field>
<field id="suma-pvm">0.00</field>
<field id="suma-su-pvm">0.00</field>
<field id="eilutes-tipas">D</field>
<field id="pvm-procentas">21</field>
</record>
<record>
<field id="abonento-tipas">GSM ryšio abonentai</field>
<field id="abonento-numeris">6666666</field>
<field id="abonento-vardas" />
<field id="mokejimo-planas">Verslas 0</field>
<field id="abonentinis-mokestis">0.00</field>
<field id="kiti-mokesciai">0.00</field>
<field id="vietiniai-balso-skambuciai">0.00</field>
<field id="vietiniai-sms">0.00</field>
<field id="vietinis-duomenu-perdavimas">0.00</field>
<field id="tarptautiniai-skambuciai">0.00</field>
<field id="tarptinkliniai-skambuciai">0.00</field>
<field id="kiti-papildomos-paslaugos">0.00</field>
<field id="nuolaida">0.00</field>
<field id="suma-be-pvm">0.00</field>
<field id="suma-pvm">0.00</field>
<field id="suma-su-pvm">0.00</field>
<field id="eilutes-tipas">S</field>
<field id="pvm-procentas" />
</record>

17 REPLIES 17
grantjenkins
Community Champion
Community Champion

Your sample XML is missing the last three closing tags which would cause an error (not sure if you just missed them when pasting in here.

 

To convert to JSON you might need to convert to XML first. I've got a Compose that just holds the XML you provided, including the closing tags.

grantjenkins_0-1667999638871.png

 

And then another Compose using the following expression:

 

json(xml(outputs('Compose')))

 

grantjenkins_1-1667999686249.png

 

I then have a Parse JSON where I pass in the output of the Compose JSON, and I copy the Body of the Compose JSON after running it so I can then paste this into the Generate from sample to get my schema.

grantjenkins_2-1668000012004.png

 

Full XML including closing tags:

 

<?xml version="1.0" encoding="UTF-8"?>
<documents>
    <document id="konsoliduota-saskaita">
        <header>
            <parameter id="kliento-pavadinimas"> "Company"</parameter>
            <parameter id="adresas1">Adress1</parameter>
            <parameter id="adresas2">Adress2</parameter>
            <parameter id="adresas3">Adress3</parameter>
            <parameter id="adresas4" />
            <parameter id="periodas">2022 09 01-2022 09 30</parameter>
            <parameter id="kliento-numeris">393134</parameter>
            <parameter id="dokumento-numeris">227651233</parameter>
            <parameter id="dokumento-data">2022 09 30</parameter>
            <parameter id="kredito-limitas">0000.00</parameter>
            <parameter id="mokejimo-terminas">2022 10 31</parameter>
            <parameter id="imones-kodas">11111111</parameter>
            <parameter id="pvm-moketojo-kodas">111111114</parameter>
            <parameter id="viso-uz-paslaugas-be-pvm">999.99</parameter>
            <parameter id="viso-suma-be-pvm">666.66</parameter>
            <parameter id="viso-suma-pvm">123.12</parameter>
            <parameter id="viso-suma-su-pvm">1111.11</parameter>
        </header>
        <details>
            <record>
                <field id="abonento-tipas">GSM ryšio abonentai</field>
                <field id="abonento-numeris">66666666</field>
                <field id="abonento-vardas" />
                <field id="mokejimo-planas">Verslas 0</field>
                <field id="abonentinis-mokestis">0.00</field>
                <field id="kiti-mokesciai">0.00</field>
                <field id="vietiniai-balso-skambuciai">0.00</field>
                <field id="vietiniai-sms">0.00</field>
                <field id="vietinis-duomenu-perdavimas">0.00</field>
                <field id="tarptautiniai-skambuciai">0.00</field>
                <field id="tarptinkliniai-skambuciai">0.00</field>
                <field id="kiti-papildomos-paslaugos">0.00</field>
                <field id="nuolaida">0.00</field>
                <field id="suma-be-pvm">0.00</field>
                <field id="suma-pvm">0.00</field>
                <field id="suma-su-pvm">0.00</field>
                <field id="eilutes-tipas">D</field>
                <field id="pvm-procentas">21</field>
            </record>
            <record>
                <field id="abonento-tipas">GSM ryšio abonentai</field>
                <field id="abonento-numeris">6666666</field>
                <field id="abonento-vardas" />
                <field id="mokejimo-planas">Verslas 0</field>
                <field id="abonentinis-mokestis">0.00</field>
                <field id="kiti-mokesciai">0.00</field>
                <field id="vietiniai-balso-skambuciai">0.00</field>
                <field id="vietiniai-sms">0.00</field>
                <field id="vietinis-duomenu-perdavimas">0.00</field>
                <field id="tarptautiniai-skambuciai">0.00</field>
                <field id="tarptinkliniai-skambuciai">0.00</field>
                <field id="kiti-papildomos-paslaugos">0.00</field>
                <field id="nuolaida">0.00</field>
                <field id="suma-be-pvm">0.00</field>
                <field id="suma-pvm">0.00</field>
                <field id="suma-su-pvm">0.00</field>
                <field id="eilutes-tipas">S</field>
                <field id="pvm-procentas" />
            </record>
        </details>
    </document>
</documents>

 

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins Yes i know it is missing i just pasted the small part from xml

 

I already have done as you have described look:

convert to XML

jja_0-1667999934696.png

 

convert XML to JSON

jja_2-1668000082261.png

 

Parse JSON

 

jja_3-1668000190976.png

And then i try in compose to reach some element but this is what i get

 

jja_4-1668000318324.png

 

jja_5-1668000357540.png

 

I can not get to the fields values

 

grantjenkins
Community Champion
Community Champion

Ah ok - sounds like a fun challenge (love working on this sort of stuff). I'm just off to bed now but will try to get what you're after tomorrow.

 

Did you just want the fields, or the parameters too?



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins 

I gues i need also a parameter cause i want to contrsct a html table where i imagine the html column name will be parameter and value will be the parameter text value

grantjenkins
Community Champion
Community Champion

Just confirming - would this JSON be appropriate for your needs? It will allow for multiple documents, and within each document, multiple records, and for each record, multiple fields. Each document would have a unique id.

 

{
    "documents": [
        {
            "id": "konsoliduota-saskaita",
            "records": [
                {
                    "abonento-tipas": "GSM ryšio abonentai",
                    "abonento-numeris": "66666666",
                    "pvm-procentas": "21"
                },
                {
                    "abonento-tipas": "XXXGSM ryšio abonentai",
                    "abonento-numeris": "XXX66666666",
                    "pvm-procentas": "XXX21"
                }
            ]
        },
        {
            "id": "konsoliduota-saskaitaV2",
            "records": [
                {
                    "abonento-tipas": "AAAGSM ryšio abonentai",
                    "abonento-numeris": "AAA66666666",
                    "pvm-procentas": "AAA21"
                },
                {
                    "abonento-tipas": "BBBGSM ryšio abonentai",
                    "abonento-numeris": "BBB66666666",
                    "pvm-procentas": "BBB21"
                }
            ]
        }
    ]
}

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

Hi @grantjenkins 

I think it would be ok. I also don't need the header i only need to access as you say access each record with it's multiple fields.

grantjenkins
Community Champion
Community Champion

@jja thanks. Also are you able to send through what you would want for your HTML table output? In your original post you said you'd have a separate HTML table for each record.



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
grantjenkins
Community Champion
Community Champion

I've got it working using xml and xpath and not converting to JSON.

 

It currently loops through each document, and within the document loops through each record, and builds an HTML table for each list of fields for the record. I'll have to finish it off tomorrow as off to sleep.

 

As an example - it looks like that below:

grantjenkins_0-1668086929232.png

 

What did you want to do with the HTML tables? Were you going to add them to an email to be sent out for each record?

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

Hey,

Looks much much better but here is the outcome i want to achieve in this view. With the HTML table i would convert it to csv or xlsx and then process further

 

jja_0-1668088430728.png

 

grantjenkins
Community Champion
Community Champion

Ok - will work on that format. Assuming you would want a separate table per document since each document has multiple records.

 

And did you want it to have all the fields, or able to choose which ones you want in the table?



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins 

i would like to choose the fields to add to a table

And the outcome shouild be only one html table. 

l

like from example the main record is abonento-numeris and this field should folow to it's right in the html table with it's data from field id="eilutes-tipas">D</field> and field id="eilutes-tipas">S</field> cause if you see abonento numeris has 2 types of eilutes-tipas. type D and type S when i have the data i will need to sum one particular field fronm eilutes-tipas S with a field from eilutes tipas D

 

grantjenkins
Community Champion
Community Champion

What about if there's more than one document? Did you mean one HTML table per document?



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins 

As far as i have examined there is only one document which has document id=konsiliduota saskaita

that in this case is a document header which i will not take data from

With xpath i want to take this part

documents/document/details/record

 

for me the main identificator of the record is abonento-numeris and i want to be able to put into a table related data to each abonento numeris

 

grantjenkins
Community Champion
Community Champion

Hopefully this is what you are after. I've used a Select to build up the dataset that you can then just pass into a Create HTML table or Create CSV table. Note that I added two documents to the XML since it had an element called Documents (just for testing purposes).

 

The full flow is below. I'll go into each of the actions.

grantjenkins_0-1668225881021.png

 

The Compose contains your XML data.

grantjenkins_1-1668225943722.png

 

The XML is another Compose which just wraps the above data into proper XML using the expression xml. Effectively, it's just telling Power Automate to treat it as proper XML. You may not need to do this if your data has already come through as XML.

xml(outputs('Compose'))

grantjenkins_2-1668226112112.png

 

I then have an Apply to each to iterate over each document contained in the XML. The expression I use here is:

xpath(outputs('XML'), '//documents/document')

grantjenkins_3-1668226194721.png

 

I then use a Select to build up the data structure for each record. The input uses the following expression:

xpath(items('Apply_to_each_document'), '//document/details/record')

grantjenkins_4-1668226275517.png

 

For each of the mappings I've used the following expression. Note that it uses first as our xpath expression will always return an array and you only want the first item. If there are no items, it will return null.

first(xpath(item(), '//field[@id="YOUR_ID"]/text()'))

 

See a full listing of the mappings for each property below:

first(xpath(item(), '//field[@id="abonento-numeris"]/text()'))
first(xpath(item(), '//field[@id="abonento-tipas"]/text()'))
first(xpath(item(), '//field[@id="abonento-vardas"]/text()'))
first(xpath(item(), '//field[@id="mokejimo-planas"]/text()'))
first(xpath(item(), '//field[@id="abonentinis-mokestis"]/text()'))
first(xpath(item(), '//field[@id="kiti-mokesciai"]/text()'))
first(xpath(item(), '//field[@id="vietiniai-balso-skambuciai"]/text()'))
first(xpath(item(), '//field[@id="vietiniai-sms"]/text()'))
first(xpath(item(), '//field[@id="vietinis-duomenu-perdavimas"]/text()'))
first(xpath(item(), '//field[@id="tarptautiniai-skambuciai"]/text()'))
first(xpath(item(), '//field[@id="tarptinkliniai-skambuciai"]/text()'))
first(xpath(item(), '//field[@id="kiti-papildomos-paslaugos"]/text()'))
first(xpath(item(), '//field[@id="nuolaida"]/text()'))
first(xpath(item(), '//field[@id="suma-be-pvm"]/text()'))
first(xpath(item(), '//field[@id="suma-pvm"]/text()'))
first(xpath(item(), '//field[@id="suma-su-pvm"]/text()'))
first(xpath(item(), '//field[@id="eilutes-tipas"]/text()'))
first(xpath(item(), '//field[@id="pvm-procentas"]/text()'))

 

You can then use the output of the Select to create your HTML and CSV tables as so:

grantjenkins_5-1668226530432.png

 

Let me know if any issues, questions.



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins 

 

How do i filter only records where eilutes-tipas="D" and return only them?

i try to build this xpath expresion but it doesn ot work

/documents/document/details/record/field[@id="eilutes-tipas"]/text()='D'

grantjenkins
Community Champion
Community Champion

You would do something like that below to get the actual record.

 

//documents/document/details/record[field[@id='eilutes-tipas']/text()='D']

 

Or if you wanted to find something that starts with a certain character(s).

 

//documents/document/details/record[starts-with(field[@id='abonento-tipas']/text(),'GSM')]

 

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
jja
Post Prodigy
Post Prodigy

@grantjenkins 

Little by little i am aproaching to final result. can you help me now how to do this:

I have filtered the array by eilutes-tipas=D but in most cases i have 2 same aray records with same -abonento-numeris but different pvm-procentas

Now i want to perform some grouping and some math functions how to do that. For example:

 

{
"abonento-numeris": "66666666",
"abonento-vardas": "Firstname Lastname",
"suma-be-pvm": "17.52",
"kiti-papildomos-paslaugos": "3.40",
"eilutes-tipas": "D",
"pvm-procentas": "21"
},
{
"abonento-numeris": "666666667",
"abonento-vardas": "Firstname Lastname",
"suma-be-pvm": "15.70",
"kiti-papildomos-paslaugos": "3.80",
"eilutes-tipas": "D",
"pvm-procentas": "Ne obj."
},

 

What i need to do is to group by either abonento-numeris or abonento-vardas

then SUM(suma-be-pvm) from both records and then substract this amount by field value kiti-papildomos-paslaugos which in this case is 3.80. This amount comes from the record where pvm-procentas pvm-procentas": "Ne obj."

So my final html or csv table should look like this in different coluymns:

Abonento numeris or abonento vardas; (suma-be-pvm+suma-be-pvm)-kiti-papildomos-paslaugos(from pvm-procentas": "Ne obj." record) and the kiti-papildomos-paslaugos(from pvm-procentas": "Ne obj." record) amount it self

Do you have an idea how to achieve this? 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,992)