cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerDapp
Advocate I
Advocate I

Parsing XML with xpath and put it to Sharepoint List as new entry / same identifiers

Hello, would you please be so kind and give me a hint?!

 

what I want to get: I have problems to get the mechanical and chemical Information out of the XML.

 

screenshot.jpg

I try to get a SharePoint list entry from XML. I am on a good way, but now I have some problems with a kind of an array in the origin-XML-file:  I can handle the things, when the identifier is in the brackets and exists only one time,

My trigger is when the XML is created on the OneDrive, I use triggerBody() in a first Compose1 and use several following Composes to get the Information. E.g. I collect by using a ComposeName and following function to get the Name out of the XML-Body in Compose1:



xpath(xml(outputs('Compose1')), 'string(/XML/IDOC/TABLE/PRODUCT/NAME)')

<XML>
 <IDOC BEGIN="1" >
  <TABLE SEGMENT="1" >
   <PRODUCT SEGMENT="1" >
    <NAME>prodA</NAME>
...

 


 

 

But,  I have no idea to handle it, when the XML identifier is same and the "real" identifier is in between.

<XML>
 <IDOC BEGIN="1" >
  <TABLE SEGMENT="1" >
   +<PRODUCT SEGMENT="1" >
   <MECHANICAL SEGMENT="1" >
    <ELEMENT>A</ELEMENT>
    <UNIT>1</UNIT>
    <VAL1>162.0</VAL1>
   </MECHANICAL>
   <MECHANICAL SEGMENT="1" >
    <ELEMENT>B</ELEMENT>
    <UNIT>2</UNIT>
    <VAL1>485</VAL1>
   </MECHANICAL>
...

 

 

The full xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<XML>
 <IDOC BEGIN="1" >
  <TABLE SEGMENT="1" >
   <PRODUCT SEGMENT="1" >
    <NAME>prodA</NAME>
    <SERIAL>9991</SERIAL>
    <CAST>0123</CAST>
    <THICKNESS>1.50 mm</THICKNESS>
    <WITDH>100.00 mm</WIDTH>
    <ORIGIN>FR</ORIGIN>
   </PRODUCT>
   <MECHANICAL SEGMENT="1" >
    <ELEMENT>A</ELEMENT>
    <UNIT>1</UNIT>
    <VAL1>162.0</VAL1>
   </MECHANICAL>
   <MECHANICAL SEGMENT="1" >
    <ELEMENT>B</ELEMENT>
    <UNIT>2</UNIT>
    <VAL1>485</VAL1>
   </MECHANICAL>
   <CHEMICAL SEGMENT="1" >
    <ELEMENT>C</ELEMENT>
    <CAST>0.014</CAST>
   </CHEMICAL>
   <CHEMICAL SEGMENT="1" >
    <ELEMENT>D</ELEMENT>
    <CAST>17.61</CAST>
   </CHEMICAL>
   <CHEMICAL SEGMENT="1" >
    <ELEMENT>E</ELEMENT>
    <CAST>0.024</CAST>
   </CHEMICAL>
   <HEADER2 SEGMENT="1" >
    <DN>0379816</DN>
    <ORDER_HEAD>005418620</ORDER_HEAD>
    <ORDER_ITEM>04</ORDER_ITEM>
   </HEADER2>
  </TABLE>
  <Document>
   <Document_Type>31</Document_Type>
   <Document_Name>20200706150907</Document_Name>
   <File_Extension>PDF</File_Extension>
   <Heat_Number>513590</Heat_Number>
   <Document_Generation_Date>20200728</Document_Generation_Date>
   <Supplying_Entity>PLANT B</Supplying_Entity>
   <Attached_Document_Content>xxxxx</Attached_Document_Content>
  </Document>
 </IDOC>
</XML>

 

Additionally I have already built a function to extract and save the embedded PDF file from this XML to OneDrive.

get the xxxx from the XML with Xpath as like as normal string, use create new file on OneDrive with Name


concat(outputs('attachment-name'),'.',outputs('attachment-type'))

 

8 REPLIES 8
fchopo
Super User II
Super User II

Hello @PowerDapp 

I'm trying to understand what you want: What do you mean when you say "I have no idea to handle it, when the XML identifier is same and the "real" identifier is in between".

 

What do you want to get? ElementA, ElementB, both?

 

Could you give more details so we can help you?

 

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

I want to have the
value of A in the column A and
value of B in column B.

 

I know how to extract A  if there is only one <element>

xpath(xml(outputs('Compose1')), 'string(/XML/IDOC/TABLE/MECHANICAL/ELEMENT)')

 

/XML/IDOC/TABLE/MECHANICAL/ELEMENT A - Value 162

/XML/IDOC/TABLE/MECHANICAL/ELEMENT B - Value 485

but I do not know how to manage this array thing when there are more rows introduced as like as the same by <element>

and I do not know how to look into the value of the same.

screenshot.jpg

 

how do I extract missing the columns A,B,C,D,E from the XML as output for creating/updating sharepoint list items.

Hello @PowerDapp and @PowerDapp2 

You could do something like this:

xml.png

 1) Get Mechanical items.

2) For each Mechanical element.

3) Get Element content.

4) Get Val1 content.

 

Hope it helps!

Ferran

 

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

looks good, and so easy, I will check.

 

sorry for 2 accounts. 2 different browsers for 2 different users is often very helpful for testing roles 😕

OK, now I have got the array working with 

 

Mechanicals A(val1 for A) & B (val1 for B)

 

how can I use the output of val1 for A to fill into field column A for sharepoint list (and same for B)?

 

something like
use 'val1' of items('apply_to_each') where element is equal to 'A'

Hello,

Instead of using compose actions called "Get Element content" and "Get Value content" you could use "set variable" action and store their values in 2 different variables. Then, use this variables to store them in a sharepoint list using "create item" sharepoint action.

Hope it helps.

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

thanks a lot for your help fchopo, I have a mistake in my logic, can you take a look again?

 

I think you mean:

 

open array:

1a. create variable with name of "get element content" A (instead of first compose inside the array) and 

1b. set created variable to "get value content" 162 (instead of second compose inside the array).

 

2 set A in sharepoint list to value of variable A. (this is not the problem when I have managed to create and fill the variable above.)

 

but I am not allowed to create a variable inside the array (must be created on top level).

 

screenshot.jpg

 

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (59,232)