This is a VTI designed to read XML files which are structured like row sets.

XML files parsed by this VTI are always processed with external entity expansion disabled and secure parser processing enabled.

There are two invocation formats provided by this VTI.

One form of this VTI takes the following arguments. This form is useful when all of the columns in the row can be constructed from data nested INSIDE the row Element.

xmlResourceName - The name of an xml file.
rowTag - The tag of the element which contains the row-structured content.
childTags - The attributes and descendant elements inside the row element which should be treated as columns.

Here is a sample declaration of this first form of the XmlVTI:

create function findbugs( xmlResourceName varchar( 32672 ), rowTag varchar( 32672 ), childTags varchar( 32672 )... )
returns table
(
     className   varchar( 32672 ),
     bugCount    int
)
language java parameter style derby_jdbc_result_set no sql
external name 'org.apache.derby.vti.XmlVTI.xmlVTI';

...and here is a sample invocation:

create view findbugs as
select *
from table
(
     findbugs
     (
         'findbugs.xml',
         'ClassStats',
         'class', 'bugs'
     )
 ) v;

select * from findbugs where bugCount != 0;

A second form of this VTI takes the following arguments. This form is useful when some of the columns in the row are "inherited" from outer elements inside which the row element nests:

xmlResourceName - The name of an xml file.
rowTag - The tag of the element which contains the row-structured content.
parentTags - Attributes and elements (to be treated as columns) from outer elements in which the rowTag is nested.
childTags - Attributes and elements (to be treated as columns) inside the row element.

Here is a sample declaration of this second form of the XmlVTI. Using the second form involves declaring an ArrayList type and a factory method too:

create type ArrayList external name 'java.util.ArrayList' language java;

create function asList( cell varchar( 32672 ) ... ) returns ArrayList
language java parameter style derby no sql
external name 'org.apache.derby.vti.XmlVTI.asList';

create function optTrace
(
    xmlResourceName varchar( 32672 ),
    rowTag varchar( 32672 ),
    parentTags ArrayList,
    childTags ArrayList
)
returns table
(
    stmtID    int,
    queryID   int,
    complete  boolean,
    summary   varchar( 32672 ),
    type        varchar( 50 ),
    estimatedCost        double,
    estimatedRowCount    int
)
language java parameter style derby_jdbc_result_set no sql
external name 'org.apache.derby.vti.XmlVTI.xmlVTI';

create view optTrace as
       select *
       from table
       (
            optTrace
            (
                '/Users/me/derby/mainline/z.xml',
                'planCost',
                asList( 'stmtID', 'queryID', 'complete' ),
                asList( 'summary', 'type', 'estimatedCost', 'estimatedRowCount' )
            )
        ) v
;

select * from optTrace
where stmtID = 6 and complete
order by estimatedCost;

Nested and Inner Type Summary

Modifier and Type	Class and Description
private class	XmlVTI.XMLErrorHandler

Field Summary

Modifier and Type	Field and Description
private DocumentBuilder	_builder
private String[]	_currentRow
private int	_firstChildTagIdx
private NodeList	_rawRows
private int	_rowCount
private int	_rowIdx
private String	_rowTag
private InputStream	_xmlResource

Constructor Summary

Access	Constructor and Description
public	XmlVTI(InputStream The xml source as an InputStream. xmlResource, String The tag of the master row Element. rowTag, int The first (0-based) tag from columnTags which is a child tag. firstChildTagIdx, String... The tags which supply column data; all of the tag positions less than firstChildTagIdx come from Elements which are outer to the rowTag element; the remaining tags, starting at firstChildTagIdx, are tags of attributes or Elements inside the rowTag Element. columnTags) Build an XmlVTI.

Access

Constructor and Description

public

XmlVTI(InputStream

The xml source as an InputStream.

xmlResource, String

The tag of the master row Element.

rowTag, int

The first (0-based) tag from columnTags which is a child tag.

firstChildTagIdx, String...

The tags which supply column data; all of the tag positions less than firstChildTagIdx come from Elements which are outer to the rowTag element; the remaining tags, starting at firstChildTagIdx, are tags of attributes or Elements inside the rowTag Element.

columnTags)

Build an XmlVTI.

Method Summary

Modifier and Type	Method and Description
public static ArrayList<String>	Returns: a list containing those items asList(String... The items to put on the list cells) Factory method to create an ArrayList<String>
public void	close() Implements java.sql.ResultSet.close. Releases this `ResultSet` object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.
private String	findColumnValue(Element rawRow, int columnNumber) Find the value of a column inside an element.
public ResultSetMetaData	getMetaData() Overrides org.apache.derby.vti.VTITemplate.getMetaData. Implements java.sql.ResultSet.getMetaData. Retrieves the number, types and properties of this `ResultSet` object's columns.
protected String	Returns: the column value as a string getRawColumn(int The 1-based column index in the row columnNumber) Implements abstract org.apache.derby.vti.StringColumnVTI.getRawColumn. Get the string value of the column in the current row identified by the 1-based columnNumber.
public boolean	next() Implements java.sql.ResultSet.next. Moves the cursor forward one row from its current position.
private void	parseRow(int rowNumber) Parse a row into columns.
private void	readRows() Fault in the list of rows.
private String	squeezeText(Element node) Squeeze the text out of an Element.
public static XmlVTI	Returns: a VTI to turn the XML text into rows xmlVTI(String The file containing the XML text fileName, String The tag on the outermost element defining the row rowTag, String... The nested attributes and elements corresponding to columns in the row childTags) This is the static method for creating functions from a file name and child tags
public static XmlVTI	Returns: a VTI to turn the XML text into rows xmlVTI(final String Name of file which holds the XML text fileName, String The base element for the row rowTag, ArrayList<String> The names of parent tags parentTags, ArrayList<String> The names of child tags childTags) This is the static method for creating functions from a file name and both parent and child tags
private static XmlVTI	Returns: a VTI to turn the XML text into rows xmlVTI(InputStream The XML text as a stream xmlResource, String The base element for the row rowTag, ArrayList<String> The names of parent tags parentTags, ArrayList<String> The names of child tags childTags) This is the static method for creating functions from an URL and both parent and child tags
public static XmlVTI	Returns: a VTI to turn the XML text into rows xmlVTIFromURL(String An URL which locates the XML text urlString, String The tag on the outermost element defining the row rowTag, String... The nested attributes and elements corresponding to columns in the row childTags) This is the static method for creating functions from an url and child tags
public static XmlVTI	Returns: a VTI to turn the XML text into rows xmlVTIFromURL(final String An URL which locates the XML text urlString, String The base element for the row rowTag, ArrayList<String> The names of parent tags parentTags, ArrayList<String> The names of child tags childTags) This is the static method for creating functions from an URL and both parent and child tags

Field Detail

_builder	back to summary
private DocumentBuilder _builder

_currentRow	back to summary
private String[] _currentRow

_firstChildTagIdx	back to summary
private int _firstChildTagIdx

_rawRows	back to summary
private NodeList _rawRows

_rowCount	back to summary
private int _rowCount

_rowIdx	back to summary
private int _rowIdx

_rowTag	back to summary
private String _rowTag

_xmlResource	back to summary
private InputStream _xmlResource

Constructor Detail

XmlVTI	back to summary
public XmlVTI(InputStream xmlResource, String rowTag, int firstChildTagIdx, String... columnTags) Build an XmlVTI. Parameters xmlResource:InputStream The xml source as an InputStream. rowTag:String The tag of the master row Element. firstChildTagIdx:int The first (0-based) tag from columnTags which is a child tag. columnTags:String[] The tags which supply column data; all of the tag positions less than firstChildTagIdx come from Elements which are outer to the rowTag element; the remaining tags, starting at firstChildTagIdx, are tags of attributes or Elements inside the rowTag Element.

XmlVTI

back to summary

public XmlVTI(InputStream xmlResource, String rowTag, int firstChildTagIdx, String... columnTags)

Build an XmlVTI.

Parameters

xmlResource:InputStream: The xml source as an InputStream.
rowTag:String: The tag of the master row Element.
firstChildTagIdx:int: The first (0-based) tag from columnTags which is a child tag.
columnTags:String[]: The tags which supply column data; all of the tag positions less than firstChildTagIdx come from Elements which are outer to the rowTag element; the remaining tags, starting at firstChildTagIdx, are tags of attributes or Elements inside the rowTag Element.

Method Detail

asList	back to summary
public static ArrayList<String> asList(String... cells) Factory method to create an ArrayList<String> Parameters cells:String[] The items to put on the list Returns:ArrayList<String> a list containing those items

asList

back to summary

public static ArrayList<String> asList(String... cells)

Factory method to create an ArrayList<String>

Parameters

cells:String[]: The items to put on the list

Returns:ArrayList<String>

a list containing those items

close back to summary

close	back to summary
public void close() throws SQLException Implements java.sql.ResultSet.close. Doc from java.sql.ResultSet.close. Releases this `ResultSet` object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. The closing of a `ResultSet` object does not close the `Blob`, `Clob` or `NClob` objects created by the `ResultSet`. `Blob`, `Clob` or `NClob` objects remain valid for at least the duration of the transaction in which they are created, unless their `free` method is invoked. When a `ResultSet` is closed, any `ResultSetMetaData` instances that were created by calling the `getMetaData` method remain accessible. Note A `ResultSet` object is automatically closed by the `Statement` object that generated it when that `Statement` object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results. Calling the method `close` on a `ResultSet` object that is already closed is a no-op. Exceptions SQLException: if a database access error occurs

public void close() throws SQLException

Implements java.sql.ResultSet.close.

Doc from java.sql.ResultSet.close.

Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

The closing of a ResultSet object does not close the Blob, Clob or NClob objects created by the ResultSet. Blob, Clob or NClob objects remain valid for at least the duration of the transaction in which they are created, unless their free method is invoked.

When a ResultSet is closed, any ResultSetMetaData instances that were created by calling the getMetaData method remain accessible.

Note

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

Calling the method close on a ResultSet object that is already closed is a no-op.

Exceptions

SQLException:: if a database access error occurs

findColumnValue	back to summary
private String findColumnValue(Element rawRow, int columnNumber) throws Exception Find the value of a column inside an element. The columnNumber is 0-based.

getMetaData back to summary

public ResultSetMetaData getMetaData() throws SQLException

Overrides org.apache.derby.vti.VTITemplate.getMetaData.

Implements java.sql.ResultSet.getMetaData.

Doc from java.sql.ResultSet.getMetaData.

Retrieves the number, types and properties of this ResultSet object's columns.

Returns:ResultSetMetaData

the description of this ResultSet object's columns

Exceptions

SQLException:: if a database access error occurs or this method is called on a closed result set

getRawColumn	back to summary
protected String getRawColumn(int columnNumber) throws SQLException Implements abstract org.apache.derby.vti.StringColumnVTI.getRawColumn. Get the string value of the column in the current row identified by the 1-based columnNumber. Parameters columnNumber:int The 1-based column index in the row Returns:String the column value as a string Exceptions SQLException: on error

getRawColumn

back to summary

protected String getRawColumn(int columnNumber) throws SQLException

Implements abstract org.apache.derby.vti.StringColumnVTI.getRawColumn.

Get the string value of the column in the current row identified by the 1-based columnNumber.

Parameters

columnNumber:int: The 1-based column index in the row

Returns:String

the column value as a string

Exceptions

SQLException:: on error

next back to summary

next	back to summary
public boolean next() throws SQLException Implements java.sql.ResultSet.next. Doc from java.sql.ResultSet.next. Moves the cursor forward one row from its current position. A `ResultSet` cursor is initially positioned before the first row; the first call to the method `next` makes the first row the current row; the second call makes the second row the current row, and so on. When a call to the `next` method returns `false`, the cursor is positioned after the last row. Any invocation of a `ResultSet` method which requires a current row will result in a `SQLException` being thrown. If the result set type is `TYPE_FORWARD_ONLY`, it is vendor specified whether their JDBC driver implementation will return `false` or throw an `SQLException` on a subsequent call to `next`. If an input stream is open for the current row, a call to the method `next` will implicitly close it. A `ResultSet` object's warning chain is cleared when a new row is read. Returns:boolean `true` if the new current row is valid; `false` if there are no more rows Exceptions SQLException: if a database access error occurs or this method is called on a closed result set

public boolean next() throws SQLException

Implements java.sql.ResultSet.next.

Doc from java.sql.ResultSet.next.

Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown. If the result set type is TYPE_FORWARD_ONLY, it is vendor specified whether their JDBC driver implementation will return false or throw an SQLException on a subsequent call to next.

If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.

Returns:boolean

true if the new current row is valid; false if there are no more rows

Exceptions

SQLException:: if a database access error occurs or this method is called on a closed result set

parseRow	back to summary
private void parseRow(int rowNumber) throws Exception Parse a row into columns.

readRows	back to summary
private void readRows() throws Exception Fault in the list of rows.

squeezeText	back to summary
private String squeezeText(Element node) throws Exception Squeeze the text out of an Element.

xmlVTI	back to summary
public static XmlVTI xmlVTI(String fileName, String rowTag, String... childTags) throws Exception This is the static method for creating functions from a file name and child tags Parameters fileName:String The file containing the XML text rowTag:String The tag on the outermost element defining the row childTags:String[] The nested attributes and elements corresponding to columns in the row Returns:XmlVTI a VTI to turn the XML text into rows Exceptions Exception: on error

xmlVTI

back to summary

public static XmlVTI xmlVTI(String fileName, String rowTag, String... childTags) throws Exception

This is the static method for creating functions from a file name and child tags

Parameters

fileName:String: The file containing the XML text
rowTag:String: The tag on the outermost element defining the row
childTags:String[]: The nested attributes and elements corresponding to columns in the row

Returns:XmlVTI

a VTI to turn the XML text into rows

Exceptions

Exception:: on error

xmlVTI	back to summary
public static XmlVTI xmlVTI(final String fileName, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception This is the static method for creating functions from a file name and both parent and child tags Parameters fileName:String Name of file which holds the XML text rowTag:String The base element for the row parentTags:ArrayList<String> The names of parent tags childTags:ArrayList<String> The names of child tags Returns:XmlVTI a VTI to turn the XML text into rows Exceptions Exception: on error

xmlVTI

back to summary

public static XmlVTI xmlVTI(final String fileName, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception

This is the static method for creating functions from a file name and both parent and child tags

Parameters

fileName:String: Name of file which holds the XML text
rowTag:String: The base element for the row
parentTags:ArrayList<String>: The names of parent tags
childTags:ArrayList<String>: The names of child tags

Returns:XmlVTI

a VTI to turn the XML text into rows

Exceptions

Exception:: on error

xmlVTI	back to summary
private static XmlVTI xmlVTI(InputStream xmlResource, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception This is the static method for creating functions from an URL and both parent and child tags Parameters xmlResource:InputStream The XML text as a stream rowTag:String The base element for the row parentTags:ArrayList<String> The names of parent tags childTags:ArrayList<String> The names of child tags Returns:XmlVTI a VTI to turn the XML text into rows Exceptions Exception: on error

xmlVTI

back to summary

private static XmlVTI xmlVTI(InputStream xmlResource, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception

This is the static method for creating functions from an URL and both parent and child tags

Parameters

xmlResource:InputStream: The XML text as a stream
rowTag:String: The base element for the row
parentTags:ArrayList<String>: The names of parent tags
childTags:ArrayList<String>: The names of child tags

Returns:XmlVTI

a VTI to turn the XML text into rows

Exceptions

Exception:: on error

xmlVTIFromURL	back to summary
public static XmlVTI xmlVTIFromURL(String urlString, String rowTag, String... childTags) throws Exception This is the static method for creating functions from an url and child tags Parameters urlString:String An URL which locates the XML text rowTag:String The tag on the outermost element defining the row childTags:String[] The nested attributes and elements corresponding to columns in the row Returns:XmlVTI a VTI to turn the XML text into rows Exceptions Exception: on error

xmlVTIFromURL

back to summary

public static XmlVTI xmlVTIFromURL(String urlString, String rowTag, String... childTags) throws Exception

This is the static method for creating functions from an url and child tags

Parameters

urlString:String: An URL which locates the XML text
rowTag:String: The tag on the outermost element defining the row
childTags:String[]: The nested attributes and elements corresponding to columns in the row

Returns:XmlVTI

a VTI to turn the XML text into rows

Exceptions

Exception:: on error

xmlVTIFromURL	back to summary
public static XmlVTI xmlVTIFromURL(final String urlString, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception This is the static method for creating functions from an URL and both parent and child tags Parameters urlString:String An URL which locates the XML text rowTag:String The base element for the row parentTags:ArrayList<String> The names of parent tags childTags:ArrayList<String> The names of child tags Returns:XmlVTI a VTI to turn the XML text into rows Exceptions Exception: on error

xmlVTIFromURL

back to summary

public static XmlVTI xmlVTIFromURL(final String urlString, String rowTag, ArrayList<String> parentTags, ArrayList<String> childTags) throws Exception

This is the static method for creating functions from an URL and both parent and child tags

Parameters

urlString:String: An URL which locates the XML text
rowTag:String: The base element for the row
parentTags:ArrayList<String>: The names of parent tags
childTags:ArrayList<String>: The names of child tags

Returns:XmlVTI

a VTI to turn the XML text into rows

Exceptions

Exception:: on error

org.apache.derby.vti back to summary

private Class XmlVTI.XMLErrorHandler

Class Inheritance

extends Object
implements ErrorHandler

java.lang.Object
org.apache.derby.vti.XmlVTI.XMLErrorHandler

All Implemented Interfaces

org.xml.sax.ErrorHandler

Constructor Summary

Access	Constructor and Description
private	XMLErrorHandler()

Method Summary

Modifier and Type	Method and Description
private void	closeInput()
public void	error(SAXParseException The error information encapsulated in a SAX parse exception. exception) Implements org.xml.sax.ErrorHandler.error. Receive notification of a recoverable error.
public void	fatalError(SAXParseException The error information encapsulated in a `SAXParseException`. exception) Implements org.xml.sax.ErrorHandler.fatalError. Receive notification of a non-recoverable, fatal error.
public void	warning(SAXParseException The warning information encapsulated in a SAX parse exception. exception) Implements org.xml.sax.ErrorHandler.warning. Receive notification of a warning.

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

Constructor Detail

XMLErrorHandler	back to summary
private XMLErrorHandler()

Method Detail

closeInput	back to summary
private void closeInput()

error	back to summary
public void error(SAXParseException exception) throws SAXException Implements org.xml.sax.ErrorHandler.error. Doc from org.xml.sax.ErrorHandler.error. Receive notification of a recoverable error. This corresponds to the definition of "error" in section 1.2 of the W3C XML 1.0 Recommendation. For example, a validating parser would use this callback to report the violation of a validity constraint. The default behaviour is to take no action. The SAX parser must continue to provide normal parsing events after invoking this method: it should still be possible for the application to process the document through to the end. If the application cannot do so, then the parser should report a fatal error even if the XML recommendation does not require it to do so. Filters may use this method to report other, non-XML errors as well. Parameters exception:SAXParseException The error information encapsulated in a SAX parse exception. Exceptions SAXException: Any SAX exception, possibly wrapping another exception.

error

back to summary

public void error(SAXParseException exception) throws SAXException

Implements org.xml.sax.ErrorHandler.error.

Doc from org.xml.sax.ErrorHandler.error.

Receive notification of a recoverable error.

This corresponds to the definition of "error" in section 1.2 of the W3C XML 1.0 Recommendation. For example, a validating parser would use this callback to report the violation of a validity constraint. The default behaviour is to take no action.

The SAX parser must continue to provide normal parsing events after invoking this method: it should still be possible for the application to process the document through to the end. If the application cannot do so, then the parser should report a fatal error even if the XML recommendation does not require it to do so.

Filters may use this method to report other, non-XML errors as well.

Parameters

exception:SAXParseException: The error information encapsulated in a SAX parse exception.

Exceptions

SAXException:: Any SAX exception, possibly wrapping another exception.

fatalError back to summary

fatalError	back to summary
public void fatalError(SAXParseException exception) throws SAXException Implements org.xml.sax.ErrorHandler.fatalError. Doc from org.xml.sax.ErrorHandler.fatalError. Receive notification of a non-recoverable, fatal error. As defined in section 1.2 of the W3C XML 1.0 Recommendation, fatal errors are those that would make it impossible for a parser to continue normal processing. These include violation of a well-formedness constraint, invalid encoding, and forbidden structural errors as described in the W3C XML 1.0 Recommendation. Parameters exception:SAXParseException The error information encapsulated in a `SAXParseException`. Exceptions SAXException: if the application chooses to discontinue the parsing

public void fatalError(SAXParseException exception) throws SAXException

Implements org.xml.sax.ErrorHandler.fatalError.

Doc from org.xml.sax.ErrorHandler.fatalError.

Receive notification of a non-recoverable, fatal error.

As defined in section 1.2 of the W3C XML 1.0 Recommendation, fatal errors are those that would make it impossible for a parser to continue normal processing. These include violation of a well-formedness constraint, invalid encoding, and forbidden structural errors as described in the W3C XML 1.0 Recommendation.

Parameters

exception:SAXParseException: The error information encapsulated in a SAXParseException.

Exceptions

SAXException:: if the application chooses to discontinue the parsing

warning	back to summary
public void warning(SAXParseException exception) throws SAXException Implements org.xml.sax.ErrorHandler.warning. Doc from org.xml.sax.ErrorHandler.warning. Receive notification of a warning. SAX parsers will use this method to report conditions that are not errors or fatal errors as defined by the XML recommendation. The default behaviour is to take no action. The SAX parser must continue to provide normal parsing events after invoking this method: it should still be possible for the application to process the document through to the end. Filters may use this method to report other, non-XML warnings as well. Parameters exception:SAXParseException The warning information encapsulated in a SAX parse exception. Exceptions SAXException: Any SAX exception, possibly wrapping another exception.

warning

back to summary

public void warning(SAXParseException exception) throws SAXException

Implements org.xml.sax.ErrorHandler.warning.

Doc from org.xml.sax.ErrorHandler.warning.

Receive notification of a warning.

SAX parsers will use this method to report conditions that are not errors or fatal errors as defined by the XML recommendation. The default behaviour is to take no action.

The SAX parser must continue to provide normal parsing events after invoking this method: it should still be possible for the application to process the document through to the end.

Filters may use this method to report other, non-XML warnings as well.

Parameters

exception:SAXParseException: The warning information encapsulated in a SAX parse exception.

Exceptions

SAXException:: Any SAX exception, possibly wrapping another exception.

public Class XmlVTI

Nested and Inner Type Summary

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail

private Class XmlVTI.XMLErrorHandler

Constructor Summary

Method Summary

Constructor Detail

Method Detail