How to get xml field value using csv column header powershell

Question

When I use for loop and then add value $b here in code ({ $_.'field_name' -eq $b} then it's giving blank value instead of result.

$CSVpath = "G:\1.csv"
$columncount=((Get-Content $CSVpath)[0] -split ",").count
write-host $columncount
$xml= "G:\1.xml"
[xml] $xmlDoc = Get-Content $xml
for($i=0; $i -le $columncount;$i++){
$a = ((Get-Content $CSVpath)[0] -split(','))[$i]
$b= "'$a'"
Function xml{
param($value)
$xml= "G:\1.xml"
[xml] $xmlDoc = Get-Content $xml
$a1= $xmlDoc.enum_types.enum_type.Where({ $_.'field_name' -eq  $b}, 'First').items.item.where({ $_.id -eq 1}).value
$d = $a1.'#cdata-section'
write-host  $d
}
xml $b
}

Please help me to resolve issue. Please find xml:

    <enum_types>
    
        <enum_type field_name="Test1">
            <items>
                <item>
                    <id>1</id>
                    <value>A</value>
                </item>
            </items>
            </enum_type>
<enum_type field_name="Test2">
            <items>
                <item>
                    <id>1</id>
                    <value>A</value>
                </item>
            </items>
            </enum_type>
        </enum_types>

Please find csv file :

Answer 1

Here's a streamlined version of your code:

$CSVpath = "G:\1.csv"
$xmlPath = "G:\1.xml"

# This is a more robust (and faster) way to load XML files.
($xmlDoc = [xml]::new()).Load((Convert-Path $xmlPath))

# Get all column names
$columnNames = (Get-Content -First 1 $CSVpath) -split ',' -replace '"'
Write-Host "Column count: $($columnNames.Count); names: $columnNames"

foreach ($columnName in $columnNames) {
  @($xmlDoc.enum_types.enum_type.Where({ $_.field_name -eq $columnName }, 'First').
   items).ForEach({ $_.item }).Where({ $_.id -eq 1 }).value
}

It outputs the following for me:

Column count: 2; names: Test1 Test2
A
A

Note:

• Since there are no CDATA sections in your XML document, there is no need for .'#cdata-section'

• The rationale for using .ForEach() to enumerate the child elements named <item>, specifically - while not strictly necessary with your sample XML document, since there's only one such child element - is explained in this answer.

  • Use of @(...), the array-subexpression operator, in order to apply .ForEach() (and .Where()) safely is only required in Windows PowerShell, due to an unfortunate bug^[1], which has since been fixed in PowerShell (Core) 7+.

  • The linked answer also shows a - faster - XPath-based solution, via Select-Xml, which you could apply analogously to an [xml] instance already in memory ($xmlDoc in this case), via its .SelectSingleNode() instance method.

---

The above only operates on the column names of the CSV file, with a hard-coded id value - as in your own attempt.

To extract data from the XML based on the data in the CSV and based on which columns are filled in:

$CSVpath = "G:\1.csv"
$xmlPath = "G:\1.xml"

# This is a more robust (and faster) way to load XML files.
($xmlDoc = [xml]::new()).Load((Convert-Path $xmlPath))

# Import the CSV.
$csvRows = Import-Csv $CSVpath
# Get the column names.
$columnNames = $csvRows[0].psobject.Properties.Name

foreach ($csvRow in $csvRows) {
  foreach ($columnName in $columnNames) {
    $id = $csvRow.$columnName
    if (-not $id) { continue }
    @($xmlDoc.enum_types.enum_type.Where({ $_.field_name -eq $columnName }, 'First').
      items).ForEach({ $_.item }).Where({ $_.id -eq $id }).value
  }
}

Note: With a nested loop like that it's definitely worth considering switching to an XPath solution with .SelectSingleNode() in order to improve performance.

^{[1] .ForEach() and .Where() should work on any object, even scalar (non-collection) ones, but in Windows PowerShell that doesn't work with certain types, such as XML elements.}