Converting a SAS or DBMS Table to an XML Table

Overview

You can use the XML Writer transformation to convert almost any data source to an XML file. For example, you can convert data in a SAS data set (SAS proprietary format) to a more generic XML format. You can also convert data from any source that SAS can access, such as a text file or a DBMS table. The XML file that is output by the writer can be generic or of a specific type (Oracle, MSAccess, and so on). The XML file is easy to share and can easily be read by any third-party software.

The target of an XML Writer must be an XML table in a SAS XML Library. Access to the library must not be set to READONLY.

Problem

You want to convert a SAS or DBMS table to an XML table in order to use the information in a third-party application.

Solution

You can create a job or update an existing job so that the SAS or DBMS table is the input to an XML Writer transformation, and an XML file is the output for the transformation. The process flow for the job would be similar to the flow in the next display.

Sample Process Flow for an XML Writer Job

In the sample flow, EMPLOYEES... (EMPLOYEES_SORTED) is an input table in SAS or DBMS format. The XML Writer transformation reads the input table and writes its content to Xsort_emp, an XML table. Assume that the SAS or DBMS input table exists in physical storage, and that the XML target table does not yet exist. The target table is created when the job is executed.

To create an XML Writer job, perform the following tasks:

Tasks

Register an XML Library for the XML Target Table

The XML Writer transformation uses a SAS XML library to access the file that contains the XML target table. Accordingly, to use the XML Writer transformation, you must have a SAS XML library that points to the file that contains the XML target table.

Perform the following steps to register an XML library that points to the file that contains an XML table:

Right-click a destination folder in the Folders tree. Then select NewLibrary from the pop-up menu to access the New Library Wizard window.
Select SAS XML Library from the list of library types. Then, click Next to access the general details page.
Enter a name and an optional description for the library. For example, the name for the library in the sample job could be EMP_XML. Then, click Next to access the Available servers page.
Select a server in the Available servers field and move it to the Selected servers field. (The server for the sample library is SASApp.) Then, click Next to access the library properties page.
Enter a libref for the new library in the Libref field and a path to an appropriate XML file in the XML File field. For the sample job, you would specify the path to the XML file (Xsort_emp.xml) that contains the target XML table. This file does not yet exist, but it is created when the job is executed. The following display shows the library properties for the sample job:

XML Library Properties

For more information about SAS XML libraries, see the SAS XML LIBNAME Engine: User's Guide.
Click Next to access the summary page. Review the details and click Finish to save the library and close the New Library Wizard window.

Register the XML Target Table

By registering the XML library, you registered the location of the XML file that contains the XML target table. You must also register the XML table itself, to specify its columns and other attributes.

The New Table wizard is used to register a table that does not yet exist in physical storage, such as a table that is created when a job is executed for the first time. This wizard enables you to copy metadata from one or more registered tables into the metadata object for the new table. For the sample job, assume that we want all columns in the input table (EMPLOYEES...) to appear in the XML target table.

Perform the following steps to register the XML target table:

Right-click a destination folder in the Folders tree. Then select NewTable from the pop-up menu to access the General Information page of the New Table window.
Enter a name and an optional description for the target table. For example, the name for the table in the sample job is Xsort_emp. Then, click Next to access the Table Storage Information page.
Use the drop-down menus in the DBMS and Library fields to select the appropriate DBMS type and library name values. (The sample job values are XML - All Documents and EMP_XML.) The following display shows the table storage information for the sample job:

XML Table Storage Information
Click Next to access the Select Columns page.
Navigate in the Available Tables field until you find the table containing the columns that you want to use for the target table definition. Then, move the columns to the Selected field. For the sample job, all of the columns in the EMPLOYEES... table are used. Click Next to access the Change Columns/Indexes page.
Review the column data for the table and make any necessary changes. Click Next to access the summary page.
Review the details and click Finish to save the table and close the New Table wizard.

Create and Populate the XML Writer Job

Perform the following steps to create and populate a job:

Create an empty SAS Data Integration Studio job.
Select and drag an XML Writer transformation from the Access folder of the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the source table from the Inventory tree. Then, drop it before the XML Writer transformation on the Diagram tab. The source table for the sample job is named EMPLOYEES... .
Drag the cursor from the source table to the input port of the XML Writer transformation. This action connects the source to the transformation.
Select and drag the XML target table from the tree view. For the sample job, the XML table is named Xsort_emp. Then drop the XML table after the XML Writer transformation on the Diagram tab.
Drag the cursor from the output port of the XML Writer transformation to the target table. This action connects the transformation to the target. The process flow looks similar to the following display:

Sample Process Flow for an XML Writer Job
Select the XML Writer transformation in the process flow on the Diagram tab. Then, click the Mapping tab on the Details pane.
Review the mappings between the Source table and Target table fields. Correct any improper mappings between the two tables. The following display shows the Mapping tab for the sample job:

XML Writer Mapping Tab

Run the Job and Verify the Results

Perform the following steps to run the job and view the output:

Right-click on an empty area of the job, and click Run in the pop-up menu. SAS Data Integration Studio generates code for the job and submits it to the SAS Application Server for execution. The following display shows a successful run of the sample job:

Sample Completed Job
If error messages display, read and respond to the messages as needed.

To review the data in the XML table, open the XML file in a Web browser or XML editor. The following display shows the table header and first data row in the XML file: Xsort_emp.xml.

XML Target Table