Hi Milosz, Sure, thanks for your help! Here are some sample native queries assuming a table called Dummy with an XML column called "attributes" that has the following schema:
<?xml version="1.0" encoding="utf-8"?> <xsd:schema version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema";> <xsd:element name="attributes" type="Attributes" /> <xsd:complexType name="Attributes"> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="unbounded" name="attribute" type="Attribute" /> </xsd:sequence> </xsd:complexType> <xsd:complexType name="Attribute"> <xsd:sequence> <xsd:choice minOccurs="1" maxOccurs="1"> <xsd:element name="val" type="xsd:string" /> <xsd:element name="attributes" type="Attributes" /> </xsd:choice> </xsd:sequence> <xsd:attribute name="name" type="xsd:string" use="required" /> </xsd:complexType> </xsd:schema> An example of a value for the xml column would be: <attributes> <attribute name="complexAttribute"> <attributes> <attribute name="simpleAttribute1"> <value>456</value> </attribute> <attribute name="simpleAttribute2"> <value>789</value> </attribute> </attributes> </attribute> </attributes> A native query with the criterion that attribute with the name "simpleAttribute1" has a val of "456" would be expressed like this: In DB2: SELECT * FROM Dummy o WHERE xmlexists('$a/attributes/attribu...@name="complexAttribute1"]/attributes/attribu...@name="simpleAttribute1"][val = $value0]' passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0") In SQL Server: DECLARE @value0 varchar(128) SET @value0=? SELECT * FROM Dummy o WHERE attributes.exist('/attributes/attribu...@name="complexAttribute1"]/attributes/attribu...@name="simpleAttribute1"][val = sql:variable("@value0")]') = 1 Let me know if you need any clarifications, thanks Stella On Sat, Jan 23, 2010 at 10:47 PM, Miłosz Tylenda <[email protected]> wrote: > Hi Stella, > > It would help if I look at sample native queries you use for collection > valued paths. Can you post a few examples for DB2 and SQL Server? > > Regards, > Milosz > > > Hi Milosz, > > > > I am using DB2 9.5 and Microsoft SQL Server 2005. > > Thank you for creating the JIRA issue, I would be very interested in > future > > support for collection valued paths as I am currently falling back on > native > > queries for such paths. > > > > Stella > > > > On Fri, Jan 15, 2010 at 8:35 PM, Milosz Tylenda <[email protected]> wrote: > > > > > Hi Stella, > > > > > > I did some looking and created a JIRA issue [1] for possible further > work. I haven't found any workaround but there are chances for the > relaxation in future versions of OpenJPA. > > > > > > If you are still interested, what database are you using? > > > > > > Cheers, > > > Milosz > > > > > > [1] http://issues.apache.org/jira/browse/OPENJPA-1465 > > > > > > > > > > Hello! > > > > > > > > I will look into this in the following weeks. If I am able to relax > the limitation or provide a workaround, I will report back here. > > > > > > > > > > > Greetings, > > > > Milosz > > > > > > > > > > > > > Hi Milosz, > > > > > > > > > > May I know if there is any workaround for this and if there are > plans to > > > > > address it? Thank you for the reference! > > > > > > > > > > > > > On Tue, Dec 15, 2009 at 5:44 AM, MiÅ‚osz wrote: > > > > > > > > > > > Hi, > > > > > > > > > > > > I guess this is an OpenJPA limitation - only queries over > single-valued > > > > > > elements are supported. There is a section on XML mapping in the > user manual > > > > > > > > > [1]. > > > > > > > > > > > > Regards, > > > > > > Milosz > > > > > > > > > > > > [1] > > > > > > > http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_xmlmapping > > > > > > > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > I am having trouble executing a JPA named query that uses > multi-valued > > > > > > > elements, within an XML column, as criteria. > > > > > > > > > > > > > > > > > I have a table with the following DB2 schema: > > > > > > > CREATE TABLE SWD.Dummy ( > > > > > > > dummyId VARCHAR(32) NOT NULL, > > > > > > > properties XML NOT NULL > > > > > > > > > > ) > > > > > > > DATA CAPTURE NONE; > > > > > > > > > > > > > > ALTER TABLE SWD.Dummy ADD CONSTRAINT Dummy_PK PRIMARY KEY > (dummyId); > > > > > > > > > > > > > > and I used JAXB to generate the beans for marshalling & > unmarshalling > > > > > > > > > from > > > > > > > the XSD schema defined for the "properties" XML column. My XSD > schema is > > > > > > as > > > > > > > follows: > > > > > > > > > > > > > > <?xml version="1.0" encoding="utf-8"?> > > > > > > > > > > <xsd:schema version="1.0" xmlns:xsd=" > http://www.w3.org/2001/XMLSchema";> > > > > > > > <xsd:element name="properties" type="propertiesType" /> > > > > > > > > > > <xsd:complexType name="propertiesType"> > > > > > > > <xsd:sequence> > > > > > > > <xsd:element maxOccurs="unbounded" name="property" > > > > > > > > > > type="propertyType" /> > > > > > > > </xsd:sequence> > > > > > > > </xsd:complexType> > > > > > > > <xsd:complexType name="propertyType"> > > > > > > > > > > <xsd:sequence> > > > > > > > <xsd:element name="keyy" type="xsd:string" /> > > > > > > > <xsd:element name="valuee" type="xsd:string" /> > > > > > > > > > > </xsd:sequence> > > > > > > > </xsd:complexType> > > > > > > > </xsd:schema> > > > > > > > > > > > > > > In summary, the element "properties" is the root element and > it contains > > > > > > > > > > any number of "property" elements which in turn may contain > "keyy" and > > > > > > > "valuee" string elements. An example of an XML compliant with > the schema: > > > > > > > > > > > > > > > > > <?xml version="1.0" encoding="UTF-16" > > > > > > > > > > > > > > ?><properties><property><keyy>abc</keyy><valuee>123</valuee></property><property><keyy>def</keyy><valuee>xyz</valuee></property></properties> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > I am able to use JPA to make simple queries on the Dummy > entity. However, > > > > > > I > > > > > > > get an error if I try to query on the "property" element. > > > > > > > > > > For example, I get an exception if I execute the following JPQL > query: > > > > > > > SELECT d FROM Dummy d, IN (d.properties.property) p WHERE > p.keyy=:keyy > > > > > > AND > > > > > > > p.valuee=:valuee > > > > > > > > > > > > > > > > > The stack trace that I get is: > > > > > > > com.example.DAOException: <openjpa-2.0.0-M3-r422266:822833 > nonfatal user > > > > > > > error> org.apache.openjpa.persistence.ArgumentException: > > > > > > > > > > collection-valued-path > > > > > > > at > com.example.DummyDAO.findDummyByProperty(DummyDAO.java:142) > > > > > > > at > com.example.GetDummyService.invoke(GetDummyService.java:63) > > > > > > > > > > ... 32 more > > > > > > > Caused by: > > > > > > > <openjpa-2.0.0-M3-r422266:822833 nonfatal user error> > > > > > > > org.apache.openjpa.persistence.ArgumentException: > collection-valued-path > > > > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.exps.AbstractExpressionBuilder.traverseXPath(AbstractExpressionBuilder.java:269) > > > > > > > at > > > > > > > > > > > > > > > > > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getPath(JPQLExpressionBuilder.java:1921) > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.addJoin(JPQLExpressionBuilder.java:741) > > > > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.evalFromClause(JPQLExpressionBuilder.java:680) > > > > > > > at > > > > > > > > > > > > > > > > > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.evalFromClause(JPQLExpressionBuilder.java:666) > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getQueryExpressions(JPQLExpressionBuilder.java:292) > > > > > > > > > > at > org.apache.openjpa.kernel.jpql.JPQLParser.eval(JPQLParser.java:67) > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.<init>(ExpressionStoreQuery.java:728) > > > > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.ExpressionStoreQuery.newDataStoreExecutor(ExpressionStoreQuery.java:170) > > > > > > > at > > > > > > > > org.apache.openjpa.kernel.QueryImpl.createExecutor(QueryImpl.java:742) > > > > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.QueryImpl.compileForDataStore(QueryImpl.java:700) > > > > > > > at > > > > > > > > > > > > > > org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:682) > > > > > > > > > > at > org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:582) > > > > > > > at > > > > > > > > > > > > > > com.ibm.ws.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:104) > > > > > > > > > > at > > > > > > > > > > > > > > com.ibm.ws.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:36) > > > > > > > at > > > > > > > > > > > > > > com.ibm.ws.jpa.management.JPATxEmInvocation.createNamedQuery(JPATxEmInvocation.java:116) > > > > > > > > > > at > > > > > > > > > > > > > > com.ibm.ws.jpa.management.JPAEntityManager.createNamedQuery(JPAEntityManager.java:332) > > > > > > > at > com.example.DummyDAO.findDummyByProperty(DummyDAO.java:137) > > > > > > > > > > ... 33 more > > > > > > > > > > > > > > I've tweaked the query many times, but am unable to get it > working. I > > > > > > > couldn't find resources online regarding this either, so I > would greatly > > > > > > > > > > appreciate any help on this, thank you! > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
