process XML with parameters

[jja]

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>

[grantjenkins]

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.

And then another Compose using the following expression:

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

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.

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>

[jja]

@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

convert XML to JSON

Parse JSON

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

I can not get to the fields values

[grantjenkins]

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?

[jja]

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

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"
                }
            ]
        }
    ]
}

[jja]

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]

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

[grantjenkins]

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:

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?

[jja]

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

[grantjenkins]

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?

[jja]

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

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

[jja]

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

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.

The Compose contains your XML data.

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'))

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')

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')

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:

Let me know if any issues, questions.

[jja]

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

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')]

[jja]

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