Add node to existing xml file

This topic contains 16 replies, has 3 voices, and was last updated by Profile photo of Robin16 Robin16 4 years, 1 month ago.

  • Author
    Posts
  • #4242
    Profile photo of Robin16
    Robin16
    Participant

    Hi,

    i'm trying to add xml node to existing xml file...

    xml node to be added

    |Test|#12!test_res|#12!Test|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\.mdf
    |Test|#12!test_res|#12!Test|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\.ldf

    xml file and path :

    [xml]$temp = Get-Content C:\temp\template.xml

    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption

    please let me know how to add it...

    Thanks

  • #4247
    Profile photo of Robin16
    Robin16
    Participant

    I tried with the following script

    [string]$dev = |CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf
    |CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf

    [xml]$temp = Get-Content C:\temp\template.xml
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild([string]$dev);
    $temp.Save("C:\temp\template.xml")

    Got the below error...please take a look..

    Method invocation failed because [System.String] doesn't contain a method named 'Appendchild'.
    At C:\Brain\TestPS1.ps1:90 char:109
    + $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild <<<< ([string]$dev); + CategoryInfo : InvalidOperation: (Appendchild:String) [], RuntimeException + FullyQualifiedErrorId : MethodNotFound

  • #4256
    Profile photo of Don Jones
    Don Jones
    Keymaster

    It's taking "device" as a string, not an XMLNode. You probably want to start with the XMLDocument docs at http://msdn.microsoft.com/en-us/library/system.xml.xmldocument%28v=vs.71%29.aspx – you're kinda into .NET Framework at this level.

  • #4271
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    Hi Venkat,

    I am going to try to help you with this as best I can.  Since you have the $temp variable as a XML Document object, you can use that to create a new element.

    $device = $temp.CreateElement("Device")
    $device.InnerXml = "|CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf"
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild($device)
    $device = $temp.CreateElement("Device")
    $device.InnerXml = "|CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf"
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild($device)

    When dealing with an XML document, you have to create a new element of the file off of the schema from the document itself.  That is what the first line does.  Then you can set attributes or add text or even additional XML to the inside of that element.  Once you have that element set up, you can append that as a child of another element.

    Wash, rinse, repeat.

    If I understood what you were trying to do, this should get you going.  If I didn't, perhaps it points you in a direction that you can find your answer.

    If you want a more indepth explanation, I have used a similar technique on a blog post when adding docicons to a Sharepoint icons file.

    Hope this helps you out!

    I walked away and forgot one important thing.  Once you append the child, it is not automatically saved back to the file.  You have to explicitly save the file back to the same file or a new file using, in your case, $temp.Save("c:\myxmlfile.xml").

  • #4291
    Profile photo of Robin16
    Robin16
    Participant

    Thanks Jonathan for your help....

    it worked well....but i have hard coded the $dev value here....

    [string]$dev = |CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf

    |CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf

     

    In reality..no one knows how many and what nodes $dev contains. We need to retrieve nodes from $dev dynamically. i have mentioned the nodes just to make you understand better.

    Actual code is :

    param
    (

    $ClientName,
    $Sourcedatabase,
    $DestServer,
    $DESTINATIONDB

    )

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    $SqlConnection.ConnectionString = "Server = $DestServer; Database = '$DESTINATIONDB'; Integrated Security = True"

    $SqlCmd.CommandText = "DECLARE @defaultDataLocation nvarchar(400)
    DECLARE @defaultLogLocation nvarchar(400)

    EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData',
    @defaultDataLocation OUTPUT

    EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog',
    @defaultLogLocation OUTPUT

    SELECT @defaultDataLocation AS 'Default Data Location',@defaultLogLocation AS 'Default Log Location' "

    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null

    foreach ($Row in $Dataset.Tables[0].Rows)
    {

    $dev1 = $($Row[0])
    $dev2 = $($Row[1])

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    $SqlConnection.ConnectionString = "Server = $ClientName; Database = $Sourcedatabase; Integrated Security = True"

    $SqlCmd.CommandText = "select f.name ,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and
    d.name = '$Sourcedatabase'"

    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null

    foreach ($Row in $Dataset.Tables[0].Rows)
    {

    if($($Row[1]).EndsWith(".ldf"))
    {
    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev2 +"|#12!"
    $dev = $dev + $($Row[1])
    $dev = $dev + "
    "
    $dev
    }
    else
    {
    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev1 +"|#12!"
    $dev = $dev + $($Row[1])
    $dev = $dev + "
    "
    $dev

    }
    }

    }

    [xml]$temp = Get-Content \\susq\prod\BAL\sqlserver\scripts\template_restore_old.xml

    #($temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device).Appendchild([string]$dev);
    < #$device = $temp.CreateElement(“device”) $device.InnerXml = “|CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf” $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild($device) $device = $temp.CreateElement(“Device”) $device.InnerXml = “|CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf” $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.device.Appendchild($device $temp.Save("\\susq\prod\BAL\sqlserver\scripts\template_restore_old.xml")#>

     

     

     

    If you can look at the above code...specifically last few lines of the code, you can see $dev has xml node information which we are going to append to xml file.

    So, i'm trying to retrieve the $dev values dynamically and append it to xml file.

    please take a look at the code and let me know your thoughts....

     

    thank you in advance..

     

  • #4294
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    You can create your $dev variables without the tags in it.  That will generate your internal text that you need.  Then you can assign $dev to the InnerXml property of the $device element I create earlier...then use AppendChild() to add the $device element back before saving.

    I would also suggest using a substitution method for generating your strings.  Using the "$dev = $dev +" method to create your strings will make it much harder to maintain.  I would create the entire string as one string, but where you have $($Row[0]) and $($Row[1]) tokens, I would replace with {0} and {1} and use something like the -format operator.

    $dev = "data!moredata{0}evenmoredata{1}lastdata" -f $Row[0], $Row[1]

    Using this type of construct will simply replace the {0} and {1} tokens with whatever is in $Row elements.  You also eliminate possibility of mistakes of having extra or missing quotes.

    I know that last part wasn't part of your original question, but I thought I would throw that one in for free.  Let me know if the first part makes sense with your original question.

  • #4337
    Profile photo of Robin16
    Robin16
    Participant

    Thank you very much Jonathan...

    it worked but only second row is appending to the file, it missed the first row.

     

    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device)
    $temp.Save("C:\temp\template_restore_old.xml")

     

    Output :

     

    |sdba|#12!testCDC|#12!SDBA_log|#12!L:\MSSQL\LOG|#12!L:\MSSQL\LOG\SDBA_log.ldf

     

     

     

  • #4344
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    it looks like you need to move this appending operation up into your foreach loop where you create the $dev variable.  You should open the XML file before the foreach loop, then run the CreateElement/InnerXml/AppendChild code just after the if..then block.  Then just after the foreach loop closes, save the file.

    Currently, you are iterating through both $dev versions, but only the last one is alive when it hits the code to append to your XML file.

  • #4345
    Profile photo of Robin16
    Robin16
    Participant

    i tried like this...

    [xml]$temp = Get-Content C:\Brain\template_restore_new.xml

    foreach ($Row in $Dataset.Tables[0].Rows)
    {

    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device) > output.txt

    if($($Row[1]).EndsWith(".ldf"))
    {

    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev2 +"|#12!"
    $dev = $dev + $($Row[1])
    #$dev
    }
    else
    {

    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev1 +"|#12!"
    $dev = $dev + $($Row[1])
    #$dev

    }
    }

    }
    $temp.Save("C:\Brain\template_restore_new.xml")

     

    now i can only see the first line...please take a look...

    i tried like this as well...but no luck...

    [xml]$temp = Get-Content C:\Brain\template_restore_new.xml

    foreach ($Row in $Dataset.Tables[0].Rows)
    {

    if($($Row[1]).EndsWith(".ldf"))
    {
    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device)
    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev2 +"|#12!"
    $dev = $dev + $($Row[1])
    #$dev
    }
    else
    {
    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device)
    $dev = "|" + $Sourcedatabase + "|#12!" + $DESTINATIONDB
    $dev = $dev + "|#12!" + $($Row[0]) +"|#12!"
    $dev = $dev + $dev1 +"|#12!"
    $dev = $dev + $($Row[1])
    #$dev

    }
    }

    }
    $temp.Save("C:\Brain\template_restore_new.xml")

     

  • #4347
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    You are close...

    Move:

    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device) > output.txt

    to just below the if..then block, and you should be golden.

  • #4348
    Profile photo of Robin16
    Robin16
    Participant

    you mean like this...btw what do you mena by block ?

     

    if($($Row[1]).EndsWith(“.ldf”))
    {
    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device)
    $dev = “|” + $Sourcedatabase + “|#12!” + $DESTINATIONDB
    $dev = $dev + “|#12!” + $($Row[0]) +”|#12!”
    $dev = $dev + $dev2 +”|#12!”
    $dev = $dev + $($Row[1])
    #$dev
    }
    else
    {
    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device)
    $dev = “|” + $Sourcedatabase + “|#12!” + $DESTINATIONDB
    $dev = $dev + “|#12!” + $($Row[0]) +”|#12!”
    $dev = $dev + $dev1 +”|#12!”
    $dev = $dev + $($Row[1])
    #$dev

    }
    }

    }
    $temp.Save(“C:\Brain\template_restore_new.xml”)

  • #4351
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    When you have code like this:

     

    if (some condition)
    {
    some code you want to run
    }
    else
    {
    some other code you want to run
    }

    That is an if..then code block or a conditional statement.  It is just referring to specific set of lines of code.

    So in your case, you should have the following (I have abbreviated the code for readability):

    if (Rows condition)
    {
    first code section
    }
    else
    {
    second code section
    }
    $device = $temp.CreateElement(“device”)
    $device.InnerXml = “$dev”
    $temp.xml.TMMsg_CreateTaskReq.taskinfo.subTasks.options.restoreOptions.sqlServerRstOption.Appendchild($device) > output.txt
    }

    }
    $temp.Save(filename)

  • #4352
    Profile photo of Robin16
    Robin16
    Participant

    Fantastic....worked well this time....thank you very much ...heres the output

    |sdba|#12!testCDC|#12!SDBA|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\SDBA.mdf
    |sdba|#12!testCDC|#12!SDBA_log|#12!L:\MSSQL\LOG|#12!L:\MSSQL\LOG\SDBA_log.ldf

    One more thing ...here if you look at the full code that in my earlier post...

    this part...

     

    $SqlCmd.CommandText = “DECLARE @defaultDataLocation nvarchar(400)
    DECLARE @defaultLogLocation nvarchar(400)

    EXEC master.dbo.xp_instance_regread
    N’HKEY_LOCAL_MACHINE’,
    N’Software\Microsoft\MSSQLServer\MSSQLServer’,
    N’DefaultData’,
    @defaultDataLocation OUTPUT

    EXEC master.dbo.xp_instance_regread
    N’HKEY_LOCAL_MACHINE’,
    N’Software\Microsoft\MSSQLServer\MSSQLServer’,
    N’DefaultLog’,
    @defaultLogLocation OUTPUT

    SELECT @defaultDataLocation AS ‘Default Data Location’,@defaultLogLocation AS ‘Default Log Location’ ”

     

    if just retrieves the default data and log file information...

    Default Data Location    Default Log Location
    D:\MSSQL\DATA            L:\MSSQL\LOG

    I need to append the $destinationDB at the end of each path....output should look something like...

    D:\MSSQL\DATA\$destinationDB.mdf

    L:\MSSQL\LOG\$destinationDB.ldf

     

    is there any way to update these names ? please let me know...

     

  • #4363
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    If I am reading correctly, it appears that $dev1 is "Default Data Location" and $dev2 is "Default Log Location".  If that is an incorrect assumption, let me know.

    You could do something like this:

    $destinationDBData = "$dev1\$destinationDB.mdf"
    $destinationDBLog = "$dev2\$destinationDB.ldf"

    When using double quotes, Powershell will do string expansion.  So $dev1 will drop in its value, $destinationDB will drop in its value, and then you have a static .mdf or .ldf at the end to create a new string from the differing variables.

  • #4366
    Profile photo of Robin16
    Robin16
    Participant

    thanks again...that worked well.....

    So for now we got the default file paths and we added .mdf and .ldf file...

    now i need to check if the destination db exists or not, if exists we need to get those data and log file for the destination db...and put it in the xml node...if not..it should take default path and add.mdf and .ldf ( which we did )...

    please let me know if i confuse you...

     

  • #4367
    Profile photo of Jonathan Tyler
    Jonathan Tyler
    Participant

    If you have some specific code that is not working, or not working well, I am happy to try to help with that.

    I'm not sure if you developed the code above yourself or cobbled it together from various sources on the web.  In either case, you need to put some time in on this as well.  Everyone on these forums have regular jobs to attend to and answer on a volunteer basis as we are able.

    Please understand that my answer is not meant to be an attack.  In order to get better at Powershell you have to put some time into it to learn how it works.  Everyone gets stuck, even me, and that is what these forums are here for...to help you get unstuck.

  • #4368
    Profile photo of Robin16
    Robin16
    Participant

    Hi Jonathan,

    I understand and agree with you.

    i'm new to powershell and coding as well....that is the reason i have been asking too many questions....

    As you said i have got the code from different sources and i have customized it according to my requirement....I don't have much time to learn powershell as i wish to...

    I'll work on it , once i get it i'll post it here for future purpose...

    Thanks again for all your help

You must be logged in to reply to this topic.