CODEWITHSUNDEEP
sqlxmlpowershell
Rishabh
Rishabh

Reputation: 21

Export SQL query results to XML format using powershell

I need to make an XML file based on the SQL query that I run using powershell. I already know the schema for the XML that I need to create. The query results need to be looped through and I want to add each data value to specific XML node as per the schema.

I am able to run the query and get the results I need but I am having issues placing the data as per prescribed format.

Here's an example of how I am trying to accomplish this:

**Parsing the XMl Template

$XmlTemplate= [xml](get-content $xml) ($xml is the schema I have from the client)

***Parsing through XML Template and jumping to tag

$PlanIDXML= $XmlTemplate.NpiLink.PlanProvider.PlanID (to get to the node I need to enter data into)

**Parsing through XML Template and jumping to tag

$PlannameXML= $XmlTemplate.NpiLink.PlanProvider.PlanName (to get to the node I need to enter data into)

sample qry; select PlanID,PlanName from plan

**Assuming I ran my query and saved the results as $qryresults***

foreach($result in $qryresults) {

$PlanID=$result.PlanID $PlanName=$result.PlanName

**Make Clone

$NewPlanIDXML=$PlanIDXML.Clone()

**Make Changes to the data

$NewPlanIDXML=$PlanID

***Append

$PlanIDXML.AppendChild($NewPlanIDXML)

* Do the same thing for Plan Name **

$PlanNameXML=$result.PlanName

} $XmlTemplate.Save('filepath')

My concern is that I need to do this for each plan or planid that I get in my query results and I need to keep generating tags and tags even and append them to orginal nodes and save the schema.

So, if my query results have 10 Plan IDs it should continue to generate new Plan ID tags and Plan Name tags.

Its not letting me append (because system.string can not be converted to system.xml). I am really stuck and if you have a better approach on how to handle this, I am all ears.

Thanks much in advance!!!

Upvotes: 0

Views: 2146

Answers (2)

Jerrad Mathews
Jerrad Mathews

Reputation: 1

Position: Named Default value: None Accept pipeline input: False Accept wildcard characters: False


### -DefaultDisplayPropertySet

Specifies one or more properties of the type. These properties are displayed by the `Format-List`,
`Format-Table`, and `Format-Custom` cmdlets when no other properties are specified.

Type the names of standard or extended properties of the type. The value of this parameter can be
the names of types that are added in the same command.

This value is effective only when there are no list, table, or custom views, respectively,
defined for the type in a `Format.ps1xml` file.

This parameter was introduced in Windows PowerShell 3.0.

```yaml
Type: System.String[]
Parameter Sets: DynamicTypeSet
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-DefaultKeyPropertySet

Specifies one or more properties of the type. These properties are used by the Group-Object and Sort-Object cmdlets when no other properties are specified.

Type the names of standard or extended properties of the type. The value of this parameter can be the jerrad Mathews of types that are added in the same command.

Upvotes: 0

latkin
latkin

Reputation: 16812

You might be overengineering this a bit. If you have a template for the XML node, just treat it as a string, popping your values in at the appropriate place. Generate some array of these nodes as strings, then join them together and save to disk.

Let's say your template looks like this (type in some tokens yourself where generated values should go):

--Template.xml---
<Node attr="##VALUE1##">
  <Param>##VALUE2##</Param>
</Node>

And you want to run some query to generate a bunch of these nodes, filling in VALUE1 and VALUE2. Then something like this could work:

$template = (gc .\Template.xml) -join "`r`n"
$val1Token = '##VALUE1##'
$val2Token = '##VALUE2##'

$nodes = foreach( $item in Run-Query)
{
   # simple string replace
   $result = $template
   $result = $result.Replace($val1Token, $item.Value1)
   $result = $result.Replace($val2Token, $item.Value2)
   $result
}

# you have all nodes in a string array, just join them together along with parent node
$newXml = (@("<Nodes>") + $nodes + "</Nodes>") -join "`r`n"

$newXml | out-file .\Results.xml

Upvotes: 2

Related Questions