DB2 and XMLQuery

Question

I have written the following query

SELECT Specie, XMLCAST(
          XMLQUERY('declare default element namespace\"http://zoo.org\";
                count(/diary/entry[@kind_type="@serious"])'
          ) AS INTEGER
           )
FROM Species;

The schema for a diary is

<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:z="http://zoo.org"
targetNamespace="http://zoo.org"&gt;


    <element name="diary">
        <complexType>
      <sequence>
            <element ref="z:entry"/>
          </sequence>
        </complexType>
    </element>

    <element name="entry">
        <complexType>
            <sequence>
                <element name="date" type="date"/> 
                <element name="keyword" type="string" maxOccurs="unbounded"/> 
                <element name="text" type="string"/> 
            </sequence>
            <attribute name="kind" type="z:kind_type" use="required"/> 
        </complexType>
    </element>


    <simpleType name="kind_type">
        <restriction base="string">
            <enumeration value="normal"/> 
            <enumeration value="serious"/>
        </restriction> 
    </simpleType>
</schema>

The query gives an error:

SQL0010N  The string constant which begins with "' ) ) FROM Species", contains no ending sign.  SQLSTATE=42603

This was translated from danish. What is wrong with this query and is the '' placed wrong?

Answer 1

whitespace missing between namespace and \"http://zoo.org\"?

declare default element namespace \"http://zoo.org\";

and probably:

@kind_type=\"serious\"

How do you execute the Query? Which connector, user interface, language ... ?

Answer 2

Instead of posting an XML Schema Definition, just post a sample XML document that illustrates the relevant structures. Here's an example I threw together based on your XSD and sample query:

<?xml version="1.0" encoding="UTF-8"?>
<diary xmlns="http://zoo.org"&gt;
<entry kind="normal">
    <date>2010-10-01</date>
    <keyword>monkey</keyword>
    <keyword>diet</keyword>
    <text>President Bongo refuses to eat carrots.</text>
</entry>

<entry kind="normal">
    <date>2010-10-02</date>
    <keyword>monkey</keyword>
    <keyword>equipment</keyword>
    <text>The locks need to be re-coded again because of Albert Chimpenheimer.</text>
</entry>
<entry kind="serious">
    <date>2010-10-03</date>
    <keyword>monkey</keyword>
    <keyword>fight</keyword>
    <text>Do NOT show "The Price Is Right" on any TVs near the monkey pen; it will make them flip out.</text>
</entry>
<entry kind="normal">
    <date>2010-10-04</date>
    <keyword>monkey</keyword>
    <keyword>toys</keyword>
    <text>Make sure there are enough vuvuzelas for all the animals.</text>
</entry>
</diary>

Your XQuery did not reference the name of the XML column that stores the diary for each species. For this example, we'll assume the column name is DIARYDOCUMENT.

The query that returns just the number of serious entries for each species looks very similar to the query you originally posted.

select specie, XMLCAST (
    XMLQUERY('declare default element namespace "http://zoo.org";
        count($DIARYDOCUMENT/diary/entry[@kind="serious"])' 
    ) 
AS INTEGER )
FROM zoo.diaries
~

So, pick a new command terminator, such as ~. Make sure the document column name is included in the XQuery, either using the actual column name or by referencing an alias that is bound to the document column in a PASSING clause. Don't escape any double quotes in the XQuery portion if it's being run from a script. If your query is being sent by a programming language that requires double quotes to be escaped, then escape all of them consistently.