Hi,
i’m trying to add xml node to existing xml file…
xml node to be added
<device>|Test|#12!test_res|#12!Test|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA.mdf</device>
<device>|Test|#12!test_res|#12!Test|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA.ldf</device>
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
I tried with the following script
[string]$dev = <device>|CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf</device>
<device>|CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf</device>
[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
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(v=vs.71).aspx - you’re kinda into .NET Framework at this level.
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.
<code>$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)</code>
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”).
Thanks Jonathan for your help…
it worked well…but i have hard coded the $dev value here…
[string]$dev = <device>|CAForms|#12!testCDC|#12!CAForms|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\CAForms.mdf</device>
<device>|CAForms|#12!testCDC|#12!CAForms_log|#12!L:\MSSQL\LOG|#12!D:\MSSQL\DATA\CAForms.ldf</device>
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 = “<device>|” + $Sourcedatabase + “|#12!” + $DESTINATIONDB
$dev = $dev + “|#12!” + $($Row[0]) +“|#12!”
$dev = $dev + $dev2 +“|#12!”
$dev = $dev + $($Row[1])
$dev = $dev + “</device>”
$dev
}
else
{
$dev = “<device>|” + $Sourcedatabase + “|#12!” + $DESTINATIONDB
$dev = $dev + “|#12!” + $($Row[0]) +“|#12!”
$dev = $dev + $dev1 +“|#12!”
$dev = $dev + $($Row[1])
$dev = $dev + “</device>”
$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…
You can create your $dev variables without the <device> 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.
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 :
<device>|sdba|#12!testCDC|#12!SDBA_log|#12!L:\MSSQL\LOG|#12!L:\MSSQL\LOG\SDBA_log.ldf</device>
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.
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”)
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.
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â€)
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)
Fantastic…worked well this time…thank you very much …heres the output
<device>|sdba|#12!testCDC|#12!SDBA|#12!D:\MSSQL\DATA|#12!D:\MSSQL\DATA\SDBA.mdf</device>
<device>|sdba|#12!testCDC|#12!SDBA_log|#12!L:\MSSQL\LOG|#12!L:\MSSQL\LOG\SDBA_log.ldf</device>
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…
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.
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…
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.
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