none
Editing an XML file - need some help with XPath methinks

    Question

  • Hi all,

    Tis question si a combination of Powershell and XML - hoping someone here is proficient in both.

    I have an XML file that looks like this:

    <?xml version="1.0" encoding="utf-8"?>
    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
     <Version>1.0</Version>
     <Properties>
     <Property>
      <PropertyName>CustomDataPath</PropertyName>
      <PropertyValue>D:\DATABASE\DATA\</PropertyValue>
     </Property>
     <Property>
      <PropertyName>CustomLogPath</PropertyName>
      <PropertyValue>D:\DATABASE\LOG\</PropertyValue>
     </Property>
     </Properties>
    </SqlCommandVariables>
    

    As you can see its a list of name-value pairs. Let's say I want to change the PropertyValue where the PropertyName=CustomLogPath. How would I go about that?

    Here's what I have so far, as you can see I'm stuck on the XPath stuff. I don't know hwo to get hold of a named element and thereafter I don't know how to change it either.

    $xml = [xml](get-content $dest) #dest contains a filename
    $root = $xml.get_DocumentElement();
    $root.Properties.Property[0] #Not right. Don't know how to get hold of a named element
    

    Any help much appreciated!

    thanks
    Jamie

     

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 2:05 PM

Answers

  • Hi Jamie,

    This sample code retrieves the node with an XPath pattern, sets the new value and saves the file. Since the xml file declares a non-prefixed Namespace, you need to add it to the xml’s NameTable with a prefix of your choice, just remember to include it in the nodes’ names used in the XPath pattern.

     

    @'

    <?xml version="1.0" encoding="utf-8"?>

    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">

     <Version>1.0</Version>

     <Properties>

     <Property>

      <PropertyName>CustomDataPath</PropertyName>

      <PropertyValue>D:\DATABASE\DATA\</PropertyValue>

     </Property>

     <Property>

      <PropertyName>CustomLogPath</PropertyName>

      <PropertyValue>D:\DATABASE\LOG\</PropertyValue>

     </Property>

     </Properties>

    </SqlCommandVariables>

    '@ | Set-Content ($file = 'C:\test.xml') -Encoding UTF8

    $srchVal = 'CustomLogPath'

    $newVal = 'D:\DATABASE\NEW'

    [xml]$xml = Get-Content $file

    $nsMgr = New-Object Xml.XmlNamespaceManager $xml.NameTable

    $prefix = 'jt'

    $uri = 'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'

    $nsMgr.AddNamespace($prefix, $uri)

    $xpath = "//${prefix}:Property[./${prefix}:PropertyName='$srchVal']"

    $node = $xml.SelectSingleNode($xpath, $nsMgr)

    $node.PropertyValue = $newVal

    $xml.Save($file)

    Get-Content $file

    Remove-Item $file -Verbose


      Robert Robelo  
    Wednesday, January 19, 2011 10:01 PM
  • Ooops :

     

    $targetProperty = $xml.SqlCommandVariables.Properties.Property | where { $_.PropertyName.equals("CustomDataPath") }

    • Proposed as answer by BertCraven Wednesday, January 19, 2011 3:31 PM
    • Marked as answer by Jamie ThomsonMVP Wednesday, January 19, 2011 3:35 PM
    Wednesday, January 19, 2011 3:30 PM
  • Actually, no need jrich, I've (finally) got the whole thing working. This does it:

    $xml = [xml]'<?xml version="1.0" encoding="utf-8"?>
    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
     <Version>1.0</Version>
     <Properties>
     <Property>
     <PropertyName>CustomDataPath</PropertyName>
     <PropertyValue>D:\DATABASE\DATA\</PropertyValue>
     </Property>
     <Property>
     <PropertyName>CustomLogPath</PropertyName>
     <PropertyValue>D:\DATABASE\LOG\</PropertyValue>
     </Property>
     </Properties>
    </SqlCommandVariables>'
    
    
    $root = $xml.get_DocumentElement();
    foreach ($property in $root.Properties.ChildNodes)
    {
    	#$property.PropertyName
    	if ($property.PropertyName -eq 'CustomLogPath')
    	{
    		$property.Propertyvalue = 'some value'
    	}
    }
    
    $root.Properties.Property[1].PropertyValue # <- This is what I wanted!!
    

    Chances are there's a much easier way of doing this but it works so I'm happy. Thanks both for the replies!

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 4:17 PM

All replies

  • OK, getting nearer. I can edit the node that I want if I refer to it using an array index:

    $xml = [xml]'<?xml version="1.0" encoding="utf-8"?>
    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
     <Version>1.0</Version>
     <Properties>
     <Property>
     <PropertyName>CustomDataPath</PropertyName>
     <PropertyValue>D:\DATABASE\DATA\</PropertyValue>
     </Property>
     <Property>
     <PropertyName>CustomLogPath</PropertyName>
     <PropertyValue>D:\DATABASE\LOG\</PropertyValue>
     </Property>
     </Properties>
    </SqlCommandVariables>'
    
    $root = $xml.get_DocumentElement();
    $root.Properties.Property[1].PropertyValue = 'some value'
    $root.Properties.Property[1]
    
    
    
    Trouble is, I'm never going to know the index. What i want to do is refer to the <Property> whose <PropertyName>="CustomLogPath"

     

    Any takers?

     

    thanks in advance

    jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 2:31 PM
  • $xml = [xml]'<?xml version="1.0" encoding="utf-8"?>
    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
     <Version>1.0</Version>
     <Properties>
     <Property>
     <PropertyName>CustomDataPath</PropertyName>
     <PropertyValue>D:\DATABASE\DATA\</PropertyValue>
     </Property>
     <Property>
     <PropertyName>CustomLogPath</PropertyName>
     <PropertyValue>D:\DATABASE\LOG\</PropertyValue>
     </Property>
     </Properties>
    </SqlCommandVariables>'

    $targetProperty = $xml.SqlCommandVariables.Properties | where { $_.PropertyName.equals("CustomDataPath") }

    Wednesday, January 19, 2011 3:25 PM
  • Ooops :

     

    $targetProperty = $xml.SqlCommandVariables.Properties.Property | where { $_.PropertyName.equals("CustomDataPath") }

    • Proposed as answer by BertCraven Wednesday, January 19, 2011 3:31 PM
    • Marked as answer by Jamie ThomsonMVP Wednesday, January 19, 2011 3:35 PM
    Wednesday, January 19, 2011 3:30 PM
  • Ooops :

     

     

    $targetProperty = $xml.SqlCommandVariables.Properties.Property | where { $_.PropertyName.equals("CustomDataPath") }

     


    cool, thanks bert. That should set be on the right track.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 3:36 PM
  • Unfortunately that copies the node value into a new property. Its not a reference back to the original value - i.e. the one I wanna change :(

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 3:41 PM
  • umm I'd think you'd want to do this

    where { $_.PropertyName -eq "CustomDataPath"}

    Wednesday, January 19, 2011 3:43 PM
  • umm I'd think you'd want to do this

    where { $_.PropertyName -eq "CustomDataPath"}


    Thanks jrich, unfortuantely doing that copies the value into a new variable. I need a reference to the node in the original XML document so that I can update it!

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 3:45 PM
  • really? that cant be right... -eq is a comparison operator and = is assignment...  I just started to read this thread, can you post the full current code you are using? is the XML on the OP still valid?

    Wednesday, January 19, 2011 3:54 PM
  • Actually, no need jrich, I've (finally) got the whole thing working. This does it:

    $xml = [xml]'<?xml version="1.0" encoding="utf-8"?>
    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">
     <Version>1.0</Version>
     <Properties>
     <Property>
     <PropertyName>CustomDataPath</PropertyName>
     <PropertyValue>D:\DATABASE\DATA\</PropertyValue>
     </Property>
     <Property>
     <PropertyName>CustomLogPath</PropertyName>
     <PropertyValue>D:\DATABASE\LOG\</PropertyValue>
     </Property>
     </Properties>
    </SqlCommandVariables>'
    
    
    $root = $xml.get_DocumentElement();
    foreach ($property in $root.Properties.ChildNodes)
    {
    	#$property.PropertyName
    	if ($property.PropertyName -eq 'CustomLogPath')
    	{
    		$property.Propertyvalue = 'some value'
    	}
    }
    
    $root.Properties.Property[1].PropertyValue # <- This is what I wanted!!
    

    Chances are there's a much easier way of doing this but it works so I'm happy. Thanks both for the replies!

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, January 19, 2011 4:17 PM
  • well, not saying its better, but, you can put most of that in one line

    $xml.get_documentelement().properties.childnodes | where {$_.propertyname -eq "CustomLogPath"}

    and you can get it ALL in one line, but this isnt safe because it will error out if there is more than one match. but if you are CERTAIN it will always return just one, you can do this..

    ($xml.get_documentelement().properties.childnodes | where {$_.propertyname -eq "CustomLogPath"} ).PropertyValue

    Wednesday, January 19, 2011 5:37 PM
  • Hi Jamie,

    This sample code retrieves the node with an XPath pattern, sets the new value and saves the file. Since the xml file declares a non-prefixed Namespace, you need to add it to the xml’s NameTable with a prefix of your choice, just remember to include it in the nodes’ names used in the XPath pattern.

     

    @'

    <?xml version="1.0" encoding="utf-8"?>

    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">

     <Version>1.0</Version>

     <Properties>

     <Property>

      <PropertyName>CustomDataPath</PropertyName>

      <PropertyValue>D:\DATABASE\DATA\</PropertyValue>

     </Property>

     <Property>

      <PropertyName>CustomLogPath</PropertyName>

      <PropertyValue>D:\DATABASE\LOG\</PropertyValue>

     </Property>

     </Properties>

    </SqlCommandVariables>

    '@ | Set-Content ($file = 'C:\test.xml') -Encoding UTF8

    $srchVal = 'CustomLogPath'

    $newVal = 'D:\DATABASE\NEW'

    [xml]$xml = Get-Content $file

    $nsMgr = New-Object Xml.XmlNamespaceManager $xml.NameTable

    $prefix = 'jt'

    $uri = 'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'

    $nsMgr.AddNamespace($prefix, $uri)

    $xpath = "//${prefix}:Property[./${prefix}:PropertyName='$srchVal']"

    $node = $xml.SelectSingleNode($xpath, $nsMgr)

    $node.PropertyValue = $newVal

    $xml.Save($file)

    Get-Content $file

    Remove-Item $file -Verbose


      Robert Robelo  
    Wednesday, January 19, 2011 10:01 PM
  • Could also just do this:

    $xml.SqlCommandVariables.Properties.Property[1].PropertyValue
    

     


    CraigMartin – Edgile, Inc. – http://identitytrench.com
    Thursday, January 20, 2011 9:43 AM
  • Could also just do this:

    $xml
    .SqlCommandVariables.Properties.Property[
    1
    ]
    .PropertyValue

     


    CraigMartin – Edgile, Inc. – http://identitytrench.com
    Thanks Craig. The whole point tho was that I didn't want to use numerical array indexes - you'll notice that that is the technique I used at the top of this thread.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, January 20, 2011 9:47 AM
  • Hi Jamie,

    This sample code retrieves the node with an XPath pattern, sets the new value and saves the file. Since the xml file declares a non-prefixed Namespace, you need to add it to the xml’s NameTable with a prefix of your choice, just remember to include it in the nodes’ names used in the XPath pattern.

     

    @'

    <?xml version="1.0" encoding="utf-8"?>

    <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars">

     <Version>1.0</Version>

     <Properties>

     <Property>

      <PropertyName>CustomDataPath</PropertyName>

      <PropertyValue>D:\DATABASE\DATA\</PropertyValue>

     </Property>

     <Property>

      <PropertyName>CustomLogPath</PropertyName>

      <PropertyValue>D:\DATABASE\LOG\</PropertyValue>

     </Property>

     </Properties>

    </SqlCommandVariables>

    '@   |  Set-Content  ( $file   =   'C:\test.xml' )   -Encoding   UTF8

    $srchVal   =   'CustomLogPath'

    $newVal   =   'D:\DATABASE\NEW'

    [xml] $xml   =  Get-Content  $file

    $nsMgr   =  New-Object  Xml.XmlNamespaceManager   $xml . NameTable

    $prefix   =   'jt'

    $uri   =   'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'

    $nsMgr . AddNamespace ( $prefix ,   $uri )

    $xpath   =   "//${prefix}:Property[./${prefix}:PropertyName='$srchVal']"

    $node   =   $xml . SelectSingleNode ( $xpath ,   $nsMgr )

    $node . PropertyValue   =   $newVal

    $xml . Save ( $file )

    Get-Content  $file

    Remove-Item  $file   -Verbose


      Robert Robelo  


    Robert,

    This is awesome. I felt sure there had to be a way of nailing this using XPath, thank you for proving me right.

    If anyone's interested here's a blog post that explains why I was trying to do this: Generate multiple SqlCmdVars files in your database projects I shall update it later to include Robert's technique.

    Regards
    Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, January 20, 2011 9:58 AM
  • Glad to help Jamie.


      Robert Robelo  
    Friday, January 21, 2011 8:20 PM