Author Posts

May 7, 2013 at 12:54 pm

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

May 7, 2013 at 12:56 pm

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

May 7, 2013 at 1:18 pm

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.

May 7, 2013 at 2:08 pm

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").

May 7, 2013 at 4:01 pm

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..

 

May 7, 2013 at 4:15 pm

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.

May 8, 2013 at 5:06 am

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

 

 

 

May 8, 2013 at 5:33 am

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.

May 8, 2013 at 6:27 am

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")

 

May 8, 2013 at 6:33 am

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.

May 8, 2013 at 6:36 am

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”)

May 8, 2013 at 6:43 am

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)

May 8, 2013 at 6:54 am

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...

 

May 8, 2013 at 8:43 am

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.

May 8, 2013 at 9:19 am

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...

 

May 8, 2013 at 9:36 am

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.

May 8, 2013 at 9:52 am

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