Home > Products > DataDirect XML Converters > Extending DataDirect XQuery using Converters

Using DataDirect XQuery to Access Legacy Data

Need to access legacy or other non-XML data formats and relational data? It's easy with DataDirect XQuery which now supports bi-directional programmatic access to virtually any non-XML data source, including EDIFACT, HL7, X12, EANCOM, IATA, flat files, and other legacy formats as XML.

Setting Up The Example Files

This section describes the prerequisites and procedures for setting up and running the DataDirect XQuery examples that use DataDirect XML Converters™ to access data in relational databases and other flat-file formats like CSV and EDI.

Prerequisites

Before you can run the DataDirect XQuery examples, you need to

• Install the DataDirect XQuery data sample. You need to use the data loader to load the data sample shipped with DataDirect XQuery into your preferred database. Instructions for this procedure are described in the DataDirect documentation. If you can successfully run the regular DataDirect XQuery examples, then this prequisite is satisfied.



• Download and Install the DataDirect XQuery™. Download DataDirect XQuery™. Once you have downloaded the components, unpack the .zip file. Finally, execute StylusStudioComponentsInstaller.jar. Where you install the DataDirect XML Converters™ components is unimportant.



• Modify setenv.bat (Windows) or setenv.sh (Linux/UNIX). You need to modify the setenv.bat or setenv.sh file to include the following statement after the line that adds the XQJExecute folder to the CLASSPATH:
  
  If you are using Windows:
  
  
  set CLASSPATH=%CLASSPATH%;deploymentcomponentsdir\bin\CustomFileSystem.jar
  
  
  If you are using Linux or UNIX, enter the following before the export statement:
  
  
  CLASSPATH=$CLASSPATH:deploymentcomponentsdir/bin/CustomFileSystem.jar
  
  
  where deploymentcomponentsdir is the directory in which you installed the DataDirect XML Converters™ components in the previous step. You need to do this in order to make DataDirect XQuery able to detect and use the DataDirect XML Converters™.
  
  The setenv.bat or setenv.sh file is located in the \examples folder where you installed DataDirect XQuery.



• Copy example files to \XQJExecute. You need to copy the following files to the \examples\XQJExecute folder where you installed DataDirect XQuery:
• csv-request.xq
• csv-joinToRelational.xq
• edi-request.xq
• edi-joinToRelational.xq
• ticker-request.edi
• request.csv



You can find the CSV and EDI source, XQuery application, and output example files here: http://www.stylusstudio.com/examples/datadirectxquery/examples.zip.

Example Descriptions

Four examples are provided to help you learn about exploiting DataDirect XML Converters™ in your DataDirect XQuery applications.

• Example 1: csv-request.xq — In this example, a CSV file contains a request for the stock holding information of one or more users. The XQuery file simply opens the CSV file as an XML document using the "adapter:CSV" URL scheme, which is interpreted by the DataDirect XML Converters™ components. This is the csv-request.xq file contents:
  
  
  doc("adapter://CSV:first=yes?request.csv")
  
  
  Here is a sample of the output from this XQuery file:
  
  
  <table>
  <row>
    <UserId>Jonathan</UserId>
    <start>2003-01-01</start>
    <end>2004-06-01</end>
  </row>
</table>

  
  Note that this example is run in the same folder where request.csv is; otherwise, the full path to the file is required, as shown here:
  
  
  doc("adapter://CSV:first=yes?file://c:/ddxq/examples/xqjexecute/request.csv")
  
  
  This is true for all examples.



• Example 2: csv-joinToRelational.xq — Using the same CSV file in Example 1, Example 2 scans the resulting list of users and fetches relevant stock holding information from a database table that contains details about the users' holdings. This is the csv-joinToRelational.xq file contents:
  
  
  declare option ddtek:serialize "indent=yes";
let $request := doc("adapter://CSV:first=yes?request.csv")/table
for $user in $request/row
return
  <portfolio UserID="{$user/UserId}">>
    { $request }
    { for $h in collection('holdings')/holdings
      where $h/userid eq $user/UserId
      return
        <stock>>
        {
        $h/stockticker,
        $h/shares
      }
      </stock≶
    }
  </portfolio>
  
  
• Example 3: edi-request.xq — In this example, the XQuery opens an EDI file as an XML document using the "adapter:CSV" URL scheme, which is interpreted by the DataDirect XML Converters™ components. The EDI file contains a request for information about one or more companies; the EDI file is in the X12 dialect (release 005030) and contains a 105 message type (Business Entity Filings). This is the edi-request.xq file contents:
  
  
  doc("adapter://EDI?ticker-request.edi")
  
  
  
• Example 4: edi-joinToRelational.xq — Using the same EDI file as Example 3, this example scans all the companies in the x12-105 message, and for each of them it fetches information from a database table. The result is a merge of data contained in the database (company name and revenues) and the the EDI message type (company address and ticker symbol). This is the edi-joinToRelational.xq file contents:
  
  
  declare option ddtek:serialize "indent=yes";
<company-details>
  {
  for $requestedCompany in
    doc("adapter://EDI?ticker-request.edi")/X12/TS_105/GROUP_2
  return
    let $companyInfo := collection('statistical')/statistical[ticker eq $requestedCompany/REF/REF02] (: REF02 is Reference Indentification :)
      return
        <company ticker="{$companyInfo/ticker}">
          {$companyInfo/companyname}
        {$companyInfo/annualrevenues}
        <address>
          <street>{$requestedCompany/N3/N301/text() (: 166: Address Information :)}</street>
          <city>{$requestedCompany/N4/N401/text() (: 19: City Name :)}</city>
          <state>{$requestedCompany/N4/N402/text() (: 156: State or Province Code :)}</state>
          <code>{$requestedCompany/N4/N403/text() (: 116: Postal Code :)}</code>
          <country>{$requestedCompany/N4/N404/text() (: 26: Country Code :)}</country>
        </address>
        </company>
  }
</company-details>

Running the Examples

To run an example, execute a statement like one of the following from inside the \examples\XQJExecute folder where you installed DataDirect XQuery.

If you are using Windows:

DataDirect XML Converters™ URL Properties

Stylus Studio provides adapters for a variety of file formats such as EDI, binary, RTF, dBase, and others. Each adapter URL scheme has its own set of properties that you can use to tune how XML is converted from non-XML files, and vice versa. Reference documentation for adapter URL properties is here:

http://www.stylusstudio.com/docs/adapters/AdapterURLProperties.pdf (PDF)

Those very same adapters can also be embedded inside your own programs. See the Data Conversion APIs section for more information.

Going in the opposite direction on the scale, the XML Pipeline suite of tools will let you visually design multi-stage XML programs, including XQuery, XSLT, Validation and other steps as necessary. It will even generate the code to drive the whole pipeline. At both ends of the scale, the XML Converters and DataDirect XQuery engine have you covered.

Prev: "Buy DataDirect XML Converters"