Need help regarding VB Code for getting some value from XML

Kunal_Jain28598 · October 23, 2025, 12:48pm

Hi Forum,
I need a Code to extract some information from XML file.
The XML file contains major data, and I have to extract some information and save it in Excel.
Can anyone help me with the solution?
Here Please check the xml written.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<impa:abcde xmlns:impl=“http://abc.com/chc/abc/profile/xml/xyz” schemaVersionNumber=“2”>
<imp:caseprofile_tr
<impa:trans_id 9021375</imp:trans_id>
<imp:trans_eff_dt2022-07-01</imp:trans_eff_dt>
<imp:trans_ty REWC</impl:trans_ty>
<imp:casepro_trckng_num xsi:nil=“true” /
<imp:acct
<imp:acct_num 3342734</imp:acct_num>
<imp:benefit
<imp:ben_op_cd DPPO1</imp:ben_op_cd>
<imp:ben_op_nm Dent</imp:ben_op_nm>
<imp:prodt_ty DPPO</imp:prodt_ty>
<imp:fund_arngmt_ty 1</imp:fund_arngmt_ty>
<imp:rtng_ty P</imp:rtng_ty>
<imp:brnc_ben_op
<imp:brnc_cd ACTIVE</imp:brnc_cd>
<imp:ben_op_cd DPPO1</imp:ben_op_cd>
<imp:brnch_ben_op_rt
<imp:age_step_cd />
<imp:anc_prodt_ty />
</imp:acct>
</imp:caseprofile_t>
</imp:abcde>

(Removed “>” because it was not taking it as link and I cant share the post)
From this we need to extract 5 values
1.)imp:acct_num
2.)imp:trans_id
3.)imp:trans_eff_dt
4.)imp:ben_op_cd
5.)imp:brnc_cd

Can anyone help me with the solution?

Yoichi · October 23, 2025, 1:15pm

Hi,

How about the following?

xns = "http://abc.com/chc/abc/profile/xml/xyz"

then

xDoc.Descendants(xns+"acct_num").First().Value

Note : xDoc is output from DeserializeXML activity.

Sample

Sample20251023-1a.zip (3.7 KB)

Regards,

Kunal_Jain28598 · October 23, 2025, 2:04pm

Hi @Yoichi
Thanks for your reply.
I would like to know why have we given
xns =******
Because in every xml this link will change.
And the data we need to extract will remain same.

Kunal_Jain28598 · October 23, 2025, 2:12pm

And also we have to just not get this values.
We need around 50 more values from same xml that I can’t provide here.
I need to save the values in datatable because I have to dump the data into excel.

Yoichi · October 23, 2025, 2:23pm

In this case, we can use xDoc.Root.Name.Namespace as the following.

I need to save the values in datatable because I have to dump the data into excel.

Can you share the file containing the exact input sample and expected output?

Regards,

Kunal_Jain28598 · October 24, 2025, 7:11am

Hi @Yoichi
I cannot share the file as I am not allowed to share the file by UiPath Forum.
The Output that you have shared is correct only thing is we need to save it in the form of Datatable.
Can’t we have any VB Code for this where we should not use the xDoc.Root.Name.Namespace and data can be saved.

Yoichi · October 24, 2025, 7:23am

Hi,

Can you try the following sample?

dt.Columns.Cast(Of DataColumn).Select(Function(dc) xDoc.Descendants(xns+dc.ColumnName).First().Value).ToArray()

Note: This will output result.xlsx.

Sample
Sample20251023-1aV2.zip (11.2 KB)

Regards,

Kunal_Jain28598 · October 24, 2025, 7:28am

Hi @Yoichi
Can’t we use anything without xns?

Yoichi · October 24, 2025, 7:41am

Your XML has namespace uri and namespace prefix (imp:). So basically it’s necessary to handle the xml with using namespace.
As another approach, in string manipulation (regex etc), it’s unnecessary to use XML namespace. However, it depends on XML file if it works well.

Kunal_Jain28598 · October 24, 2025, 8:36am

Hi @Yoichi
Can you help me with Regex?

Yoichi · October 24, 2025, 8:44am

For example, we want to extract value between <imp:acct_num> and </imp:acct_num>, the following will work.

System.Text.RegularExpressions.Regex.Match(inputString,"(?<=\<imp:acct_num\>)[^<]*").Value

Please note that In the case of string manipulation, it may not be possible to handle variations in XML representation.

Regards,

Kunal_Jain28598 · October 24, 2025, 8:56am

Hi @Yoichi
Any stable solution for this?
We need a solution that is more aligned and stable

Yoichi · October 24, 2025, 8:59am

I think parsing XML approach is the best.

Kunal_Jain28598 · October 24, 2025, 9:30am

And how to do that?
Can you share the steps?

sudster · October 24, 2025, 9:51am

Hi @Kunal_Jain28598 ,
Please give this a try

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<imp:abcde xmlns:imp="http://abc.com/chc/abc/profile/xml/xyz" schemaVersionNumber="2">
<imp:caseprofile_tr>
    <imp:trans_id> 9021375</imp:trans_id>
    <imp:trans_eff_dt>2022-07-01</imp:trans_eff_dt>
    <imp:trans_ty>REWC</imp:trans_ty>
    <imp:casepro_trckng_num />
    <imp:acct>
        <imp:acct_num>3342734</imp:acct_num>
        <imp:benefit />
        <imp:ben_op_cd>DPPO1</imp:ben_op_cd>
        <imp:ben_op_nm>Dent</imp:ben_op_nm>
        <imp:prodt_ty>DPPO</imp:prodt_ty>
        <imp:fund_arngmt_ty>1</imp:fund_arngmt_ty>
        <imp:rtng_ty>P</imp:rtng_ty>
        <imp:brnc_ben_op />
        <imp:brnc_cd>ACTIVE</imp:brnc_cd>
        <imp:ben_op_cd>DPPO1</imp:ben_op_cd>
        <imp:brnch_ben_op_rt />
        <imp:age_step_cd />
        <imp:anc_prodt_ty />
    </imp:acct>
</imp:caseprofile_tr>
</imp:abcde>

Kunal_Jain28598 · October 24, 2025, 1:04pm

Hi @Yoichi and @sudster
Here is a change in the xml
Actually, there are some contexts that will occur more than once

schemaVersionNumber=“2”>
<imp:caseprofile_tr
<imp:trans_id 9021375</imp:trans_id>
<imp:trans_eff_dt 2022-07-01</imp:trans_eff_dt>
<imp:trans_ty REWC</impl:trans_ty>
<imp:casepro_trckng_num xsi:nil=“true” />
<imp:acct
<imp:acct_num 3342734</imp:acct_num>
<imp:benefit
<imp:ben_op_cd DPPO1</imp:ben_op_cd>
<imp:ben_op_nm Dent</imp:ben_op_nm>
<imp:prodt_ty DPPO</imp:prodt_ty>
<imp:fund_arngmt_ty 1</imp:fund_arngmt_ty>
<imp:rtng_ty P</imp:rtng_ty>
<imp:brnc_ben_op
<imp:brnc_cd ACTIVE</imp:brnc_cd>
<imp:ben_op_cd DPPO1</imp:ben_op_cd>
<imp:brnch_ben_op_rt
<imp:age_step_cd />
<imp:anc_prodt_ty />
</imp:acct>
<impl:brnc_cd COBRA</impl:brnch_cd>
<impl:ben_op_cd DPPO1</impl:ben_opt_cd>
<impl:brnch_ben_op_rt
impl:brnc_cd>0000</impl:brnch_cd>
<impl:brnch_nm All Employees</impl:brnch_nm>
</impl:branch>
</impl:acct>
impl:brnc_cd>00001</impl:brnch_cd>
<impl:brnch_nm All Employees</impl:brnch_nm>
</impl:branch>
</impl:acct>
</imp:caseprofile_t>
</imp:abcde>

Brnch will occur more than once, so we need to save it accordingly.
We don’t know how many times brnch will occur, so can’t give it in data table as column name
Then how will we get to solve this?

Kunal_Jain28598 · October 24, 2025, 2:57pm

@Yoichi @sudster
O/P has to be in such a format.

Account Number	Trasaction ID	Effective Date	Ben_Op_CD	brnch_CD
332734	902715	2022-07-01	DPP01	ACTIVE
332734	9027175	2022-07-01	DPP01	COBRA

If brnch_CD is same than all the values will be copied in the next cell along with new brnch_CD

sudster · October 25, 2025, 3:29am

@Kunal_Jain28598 ,
Please upload a syntactically correct xml sample file.

Topic		Replies	Views
Extract data from xml to a datatable Studio studio , question , activities_panel	7	1649	September 2, 2022
Extract Element Values from SOAP XML (VB.Net / C#) Studio uiautomation , xml	29	4208	September 23, 2021
Extracting table from txt file Studio	26	2233	June 18, 2021
Extract document data (pdf) to excel Activities excel , pdf , help , extract-document-data , extract-doc	11	100	March 20, 2025
Uso de tabla en Extract Document Data Activities excel , pdf , mail , studio , question , pdf-extraction , extractdocument-data	23	90	March 18, 2025

Need help regarding VB Code for getting some value from XML

Related topics