Change xml value from excel sheet

Help Studio
, ,
1

Hi guys, I want change XML value from the input of excel, I request how to resolve this solution.

2

@Majunu09
May we ask you to share the sample XML along with the info what is to replace with which particular data. Thanks

Also have a look here:

3

HMIEXP0003321-22_2130000285.xml (538 Bytes)
I want to change each and every node values.

4

from this excel
Template.xlsx (8.6 KB)

5

I want to delete or remove the old value of xml and update it from excel.

6

apologies can you explain me about dleon.xaml file?

7

@ppr , looks fine but inside variable itself it changes and how to write again on xml file?

8

Which XML field is to update for the Excel column “Exporter Type”

  • Read Text activity - read in XML file
  • Deserialize the XML into a XDoxument . xDoc

In general it will be

  • iterate over all columns from Excel row
  • fetch the corresponding XML Element e.g by
    xDoc.Element(“Header”).Element(“YourElementName”) - myXElement
    check if myXELement is not null with isNothing(…) Function
    update it with myXElement.Value = newValueFromExcel

Saving the updated XML can be done with write text file and xDoc to String as content
Or with the xDoc.Save Method used within an invoke method acitvity

9

can you share an example like screenshot?

10

please let questions not unanswered. Thanks

11

Receiver node.

12

@ppr , help me updating this two nodes “Sender” & “Generator” on same xml file.

13

@ppr Can Share the screenshot of the solution.

  • fetch the corresponding XML Element e.g by
    xDoc.Element(“Header”).Element(“YourElementName”) - myXElement
    check if myXELement is not null with isNothing(…) Function
    update it with myXElement.Value = newValueFromExcel

Saving the updated XML can be done with write text file and xDoc to String as content
Or with the xDoc.Save Method used within an invoke method acitvity

14

@ppr , I am getting this error from the solution,
PDF_excel_XMl.xaml: ‘String’ does not have a public instance method named ‘SetValue’ matching the parameter types, generic type arguments, and generic type constraints supplied to InvokeMethod ‘Invoke Method’.

15

grafik

the Value Property was used for the update not a method.
Find docu link here:

16

@Majunu09

as the Excel Column Names and the XML Element names are not allways the same (less effort if we could rely), so we take care about this mapping.

also is not confired if only 1 record is to update or multiple records can be present and needs updated.

Prototype: 1 recored update case

  • read range - read in Excel | dtData

  • Read Text activity - read in XML file

  • Deserialize the XML into a XDoxument . - xDoc

  • Build datatable - Set up a mapping config - dtMapConfig
    grafik

retrieve first Header Element:
grafik

Update Values:
grafik

xeHeader.Element(row(“XMLElementName”).toString).Value = dtData.Rows(0)(row(“EXCELColName”).toString).toString

grafik

grafik
grafik697×560 52.9 KB

17

@ppr , sorry I m confused what is the meaning of “YourFullFilePathtoXMLFile”.
Explain please.

18

@ppr , I am getting error on set up mapping.
Error ERROR Validation Error Compiler error(s) encountered processing expression “BDT1”.
Value of type ‘DocumentFormat.OpenXml.InkML.Mapping’ cannot be converted to ‘System.Data.DataTable’. PDF_excel_XMl.xaml or else can you show how to mapping up.

19

Error ERROR Validation Error Compiler error(s) encountered processing expression “xeHeader.Element(row(“Sender”).toString).Value”.
‘Element’ is not a member of ‘String’. PDF_excel_XMl.xaml , error on assign.

20

@ppr , Can you share the xaml file?