Using OpenXml to map hierarchic XML to SQL

The ForXmlTree function described in “Using ForXmlTree to map SQL data to hierarchic XML” maps a collection of SQL tables or result sets to a hierarchic XML document. The OpenXml function reverses this process, and extracts the data for a SQL table from an input XML document.

OpenXml is similar to the xmlextract function, introduced in Adaptive Server 12.5.1, which extracts a specified data value from a given XML document. xmlextract specifies an XML document and a single XPath query expression. It returns the result of applying the XPath query to the XML document.

The Java-based OpenXml function:

Can be invoked from either a client command line or a client Java application. It is not intended for use in the SQL server.
Requires arguments that include the specified XML document and a set of options that specify the XPath query that extracts the desired output rows and the Xpath queries that extract the desired columns in each output row.

Thus, you can regard OpenXml as a two-dimensional xmlextract.

OpenXml performs either or both of these actions:

Generates a SQL script to create and populate a SQL table with the extracted data.
Executes that script to create the SQL tables with the extracted data.

The following examples assume that the XML document in “Sample data and its tree-structured XML representation” is stored in example-document.xml.

Example 1

This example shows four client command line calls to extract the depts, emps, emp_phones, and projects tables from the XML document.

java jcs.xmlutil.OpenXml -i "file:example-document.xml" \
     -r "file:depts.opt" -o "depts.sql"

java jcs.xmlutil.OpenXml -i "file:example-document.xml" \
       -r "file:emps.opt" -o "emps.sql" 

java jcs.xmlutil.OpenXml -i "file:example-document.xml" \
       -r "file:emp-phones.opt" -o "emp-phones.sql"

java jcs.xmlutil.OpenXml -i "file:example-document.xml" \
       -r "file:projects.opt" -o "projects.sql"

Example 2

This example shows the contents of the options that the command line calls in Example 1 reference. These options specify the data that the calls for OpenXml should extract, and the SQL table in which they should be stored.

-- Content of input file "depts.opt
"tablename='depts_ext' 
rowpattern='//dept' 
columns=
  '   dept_id char( 4 ) "/@dept_id"
      dept_name varchar(50) "/@dept_name" ' 

-- Content of input options file "emps.opt"
tablename='emps_ext'
rowpattern='//dept/emps/emp'
columns=
  '   emp_id char( 4 ) "/emp_id/text()"
      emp_name varchar(50) "/emp_name/text()"
       dept_id char(4) "/../../@dept_id"
       salary dec(7,2) "/salary/text()" 

'-- Content of input options file "emp-phones.opt"
tablename='emp_phones_ext'
rowpattern='/sample/dept/emps/emp/phone'
columns=  '   emp_id char( 4 ) "/../emp_id/text()"
       phone_no varchar(20) "/@phone_no" '

--Content of input options file "projects.opt"
tablename='projects_ext'
rowpattern='//dept/projects/project'
columns=
  '   project_id char( 4 ) "/project_id/text()"
      dept_id char(4) "/../../@dept_id"
      budget dec(7,2) "/budget/text()" '

Example 3

This example shows the SQL script generated by the first OpenXml call. The script creates and populates a table with the extracted depts table data. Subsequent OpenXml calls, shown in Example 1, generate similar scripts for the emps, emp_phones, and projects data.

-- output file depts.sql

create table depts_ext
    (dept_id char( 4 ) null, dept_name varchar(50) null )

insert into depts_ext values('D123', 'Main')

insert into depts_ext values('D234', 'Auxiliary')

insert into depts_ext values('D345', 'Repair')