[
https://issues.apache.org/jira/browse/DERBY-6256?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6256:
---------------------------------
Attachment: derby-6256-02-aa-allowParentTags.diff
Attaching derby-6256-02-aa-allowParentTags.diff. This patch adds the capability
for an XmlVTI to include attribute/element content from outer elements.
When people design hierarchical data, they often want inner data to inherit
context from the outer levels in which the inner data nests. This patch lets
you specify that some of the columns in the returned row should come from data
in outer elements which the row element is nested inside.
Here is additional header javadoc explaining how to declare an XmlVTI
containing content from outer elements:
* <p>
* 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:
* </p>
*
* <ul>
* <li>xmlResourceName - An URL identifying an xml resource.</li>
* <li>rowTag - The tag of the element which contains the row-structured
content.</li>
* <li>parentTags - Attributes and elements (to be treated as columns) from
outer elements in which the rowTag is nested.</li>
* <li>childTags - Attributes and elements (to be treated as columns) inside
the row element.</li>
* </ul>
*
*
* <p>
* 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:
* </p>
*
* <pre>
* 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
* (
* 'file:///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 * </pre>
Touches the following files:
M java/engine/org/apache/derby/vti/XmlVTI.java
> Move the XmlVTI into the product.
> ---------------------------------
>
> Key: DERBY-6256
> URL: https://issues.apache.org/jira/browse/DERBY-6256
> Project: Derby
> Issue Type: Improvement
> Components: SQL, Tools
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-6256-01-aa-move-XmlVTI-into-product.diff,
> derby-6256-02-aa-allowParentTags.diff
>
>
> The XmlVTI under derbyDemo has been useful to me for many years. It has
> become even more useful now that Derby supports varargs. That is because
> varargs make it very easy to declare an XmlVTI. At this point, I think it is
> worth re-phrasing the XmlVTI in terms of varargs and moving it into the
> product so that we can use it for internal table functions. There is no rush
> to expose XmlVTI as part of Derby's public api, but we could consider doing
> that if other people find this table function to be useful.
> The XmlVTI is a table function which turns an xml file into a tabular data
> set which you can query via sql. When you declare an XmlVTI, you state the
> following arguments:
> 1) The url of an xml file.
> 2) The name of the element in the xml file which you want to treat as a
> record or row.
> 3) The names of the attributes and subelements of that record which you want
> to treat as columns. Now that we have varargs, it is possible to represent
> this trailing argument as a variable length argument list.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
