How to 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

Connecting to Local or Cloud-Stored (Box, Google Drive, Amazon S3, SharePoint) XML Files

CData Drivers let you work with XML files stored locally and stored in cloud storage services like Box, Amazon S3, Google Drive, or SharePoint, right where they are.

Setting connection properties for local files

Set the URI property to local folder path.

Setting connection properties for files stored in Amazon S3

To connect to XML file(s) within Amazon S3, set the URI property to the URI of the Bucket and Folder where the intended XML files exist. In addition, at least set these properties:

• AWSAccessKey: AWS Access Key (username)
• AWSSecretKey: AWS Secret Key

Setting connection properties for files stored in Box

To connect to XML file(s) within Box, set the URI property to the URI of the folder that includes the intended XML file(s). Use the OAuth authentication method to connect to Box.

Dropbox

To connect to XML file(s) within Dropbox, set the URI proprerty to the URI of the folder that includes the intended XML file(s). Use the OAuth authentication method to connect to Dropbox. Either User Account or Service Account can be used to authenticate.

SharePoint Online (SOAP)

To connect to XML file(s) within SharePoint with SOAP Schema, set the URI proprerty to the URI of the document library that includes the intended XML file. Set User, Password, and StorageBaseURL.

SharePoint Online REST

To connect to XML file(s) within SharePoint with REST Schema, set the URI proprerty to the URI of the document library that includes the intended XML file. StorageBaseURL is optional. If not set, the driver will use the root drive. OAuth is used to authenticate.

Google Drive

To connect to XML file(s) within Google Drive, set the URI property to the URI of the folder that includes the intended XML file(s). Use the OAuth authentication method to connect and set InitiateOAuth to GETANDREFRESH.

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

The Connection, Table, and Columns are appended to the results in order to facilitate piping results from one of the CData Cmdlets directly into another one.

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!