Get values from Json and update in XML file using powrshell?

Question

I want to get values from json file and then store it to the child element in XML file by using powerShell.

[
  {
    "Modules/ScriptRunnerClassic/Default.aspx":  "steve,spenczy",
    "Modules/SiteTools/SiteUrls.aspx":  "steve,spenczy"
  }
]

This is a json from where I'll get the location/s and value/s(steve,spenczy) and below is the XML where I want to store those json values.

<?xml version="1.0" encoding="UTF-8"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <location path="Modules/ScriptRunnerClassic/Default.aspx" xdt:Locator="Match(path)">
        <system.web>
            <authorization>
                <allow users="jimmy,jhon,dev" xdt:Transform="SetAttributes(users)"/>
                <deny users="*" />
            </authorization>
        </system.web>
    </location>
    <location path="Modules/SiteTools/SiteUrls.aspx" xdt:Locator="Match(path)">
        <system.web>
            <authorization>
                <allow users="jimmy,jhon,dev" xdt:Transform="SetAttributes(users)"/>
                <deny users="*" />
            </authorization>
        </system.web>
    </location>
</configuration>

Find the location that was in json file and put its value in <allow users="jimmy,jhon,dev" .../>. It is something like update <allow users="" WHERE <location path="" The output should look like below <allow users="steve,spenczy" .../>:

<?xml version="1.0" encoding="UTF-8"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <location path="Modules/ScriptRunnerClassic/Default.aspx" xdt:Locator="Match(path)">
        <system.web>
            <authorization>
                <allow users="steve,spenczy" xdt:Transform="SetAttributes(users)"/>
                <deny users="*" />
            </authorization>
        </system.web>
    </location>
    <location path="Modules/SiteTools/SiteUrls.aspx" xdt:Locator="Match(path)">
        <system.web>
            <authorization>
                <allow users="steve,spenczy" xdt:Transform="SetAttributes(users)"/>
                <deny users="*" />
            </authorization>
        </system.web>
    </location>
</configuration>

cause there may be "location path" and "allow user" updated or may be more than 2 "location path" and "allow user" available in json file and I don't know much about xml. — Ketul Radadiya
– Ketul Radadiya, Commented Aug 5, 2021 at 10:33

Frenchy · Accepted Answer · 2021-08-05 13:49:34Z

1

you could use xpath to locate node in xml file: i have put your xml file in test.xml

$MyJsonVariable = @"
[
  {
    "Modules/ScriptRunnerClassic/Default.aspx":  "steve,spenczy",
    "Modules/SiteTools/SiteUrls.aspx":  "steve,spenczy"
  }
]
"@

$MyJsonVariable = $MyJsonVariable | ConvertFrom-JSON    
$myDefaultVar = $MyJsonVariable[0].'Modules/ScriptRunnerClassic/Default.aspx'
$mySiteVar = $MyJsonVariable[0].'Modules/SiteTools/SiteUrls.aspx'

$filePathToTask = "C:\users\itki4060\Documents\test.xml"
$xml = New-Object XML
$xml.Load($filePathToTask)

#catch all location nodes
$elements =  $xml.SelectNodes("//location")    
for($i = 0; $i -lt $elements.Count;$i++)
{
    $path = $elements[$i].GetAttribute("path");
    $allow = $elements[$i].SelectSingleNode(".//allow")
    if($path.Contains("Default.aspx"))
    {
        $allow.SetAttribute("users", $myDefaultVar);
    }
    elseif($path.Contains("SiteUrls.aspx"))
    {
         $allow.SetAttribute("users", $mySiteVar);
    }
}
$xml.Save("C:\users\itki4060\Documents\result.xml")

Instead a for loop, you could use foreach loop

foreach($elt in $elements)
{
    $path = $elt.GetAttribute("path");
    $allow = $elt.SelectSingleNode(".//allow")
    if($path.Contains("Default.aspx"))
    {
        $allow.SetAttribute("users", $myDefaultVar);
    }
    elseif($path.Contains("SiteUrls.aspx"))
    {
         $allow.SetAttribute("users", $mySiteVar);
    }
}

edited Aug 5, 2021 at 13:49

answered Aug 5, 2021 at 11:56

Frenchy

17.1k3 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ketul Radadiya Over a year ago

This will work on the powerShell versions < 5.1.1, but I have figured out to execute on latest powershell version. Thanks for the help.

Frenchy Over a year ago

i cant guess what version of powershell you are using...but the logic of the solution is the same

Ketul Radadiya · Accepted Answer · 2021-08-05 14:55:45Z

0

Hope it'll help others...

# load json into ps object
$JsonObject = Get-Content -Path "input.json" | ConvertFrom-Json

# load xml into ps object
$xmlObject = New-Object System.Xml.XmlDocument
$xmlObject.Load("output.xml")

# Store allowed users list from different locations
$path1Users = $JsonObject.'Modules/ScriptRunnerClassic/Default.aspx'
$path2Users = $JsonObject.'Modules/SiteTools/SiteUrls.aspx'

# Select the node for update
$elements = $xmlObject.SelectNodes("//location")

for($i = 0; $i -lt $elements.Count;$i++)
{
    $path = $elements[$i].path
    $allow = $elements[$i].SelectSingleNode(".//allow")
    if($path.Contains("Default.aspx"))
    {
        $allow.users = $path1Users
    }
    elseif($path.Contains("SiteUrls.aspx"))
    {
         $allow.users = $path2Users
    }
}

# Save the document
$xmlObject.Save("output.xml")

answered Aug 5, 2021 at 14:55

Ketul Radadiya

1443 silver badges13 bronze badges

1 Comment

Frenchy Over a year ago

could you close the question by validating your or my answer? thanks

Collectives™ on Stack Overflow

Get values from Json and update in XML file using powrshell?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related