Pipe XML Data to CSV in PowerShell

The CData Cmdlets Module for XML is a standard PowerShell module offering straightforward integration with XML. Below, you will find examples of using our XML Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your XML Data

See the Getting Started chapter in the data provider documentation to authenticate to your data source: The data provider models XML APIs as bidirectional database tables and XML files as read-only views (local files, files stored on popular cloud services, and FTP servers). The major authentication schemes are supported, including HTTP Basic, Digest, NTLM, OAuth, and FTP. See the Getting Started chapter in the data provider documentation for authentication guides.

After setting the URI and providing any authentication values, set DataModel to more closely match the data representation to the structure of your data.

The DataModel property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

• Document (default): Model a top-level, document view of your XML data. The data provider returns nested elements as aggregates of data.
• FlattenedDocuments: Implicitly join nested documents and their parents into a single table.
• Relational: Return individual, related tables from hierarchical data. The tables contain a primary key and a foreign key that links to the parent document.

See the Modeling XML Data chapter for more information on configuring the relational representation. You will also find the sample data used in the following examples. The data includes entries for people, the cars they own, and various maintenance services performed on those cars.

$conn = Connect-XML  -URI "$URI" -DataModel "$DataModel"

Selecting Data

Follow the steps below to retrieve data from the people table and pipe the result into to a CSV file:

Select-XML -Connection $conn -Table people | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\mypeopleData.csv -NoTypeInformation

You will notice that we piped the results from Select-XML into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

Deleting Data

The following line deletes any records that match the criteria:

Select-XML -Connection $conn -Table people -Where "[ personal.name.last ] = Roberts" | Remove-XML

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into XML, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MypeopleUpdates.csv | %{
  $record = Select-XML -Connection $XML -Table people -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-XML -Connection $xml -Table people -Columns ("[ personal.name.first ]","[ personal.name.last ]") -Values ($_.[ personal.name.first ], $_.[ personal.name.last ]) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-XML -Connection $xml -Table people -Columns ("[ personal.name.first ]","[ personal.name.last ]") -Values ($_.[ personal.name.first ], $_.[ personal.name.last ])
  }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!