Using xml-functions in h2 database for university project

Question

For my university-project I installed an h2database locally on my computer. I have a little data in it and now I am trying to export parts of this data via the included xml-functions that h2 offers.

However I do not really get how this works. I am working with SQL Workbench/J to test and after it works I implement it in my java program.

For example I have the XMLNODE-function which works like this (taken from the h2 manual):

XMLNODE(elementString [, attributesString [, contentString [,indentBoolean]]])

Create an XML node element. An empty or null attribute string means no attributes are set. An empty or null content string means the node is empty. The content is indented by default if it contains a newline. This method returns a string. Example:

CALL XMLNODE('a', XMLATTR('href', '``https://h2database.com``'), 'H2')

So when I call CALL XMLNODE('a', XMLATTR('href', 'https://h2database.com'), 'H2') I get:

<a href="https://h2database.com">H2</a>

all fine...

But when I only use the XMLNODE without the XMLATTR like this: CALL XMLNODE('a', 'H2'); I get the following output:

<aH2/> 

So I dont know what I am doing wrong cause I do not even come to the point where I can put some data in it cause when I only use the NODE it does not seem like good xml^^

Btw: When I am trying to get the resultset via my java program it just tells me that there is no data. here is the code:

s = conn.createStatement();
String query = "CALL XMLNODE('test1', XMLATTR('test2', 'test3'), 'test4');";
rs = s.executeQuery(query);


String val = rs.getString(1);
System.out.println(val);

The explanation in the manual says that it is returning a String

Answer 1

I fixed it! Here is a good example of how to use it: https://github.com/h2database/h2database/blob/master/h2/src/test/org/h2/samples/newsfeed.sql

CREATE TABLE VERSION(ID INT PRIMARY KEY, VERSION VARCHAR, CREATED VARCHAR);
INSERT INTO VERSION VALUES

(154, '2.1.210', '2022-01-17'),
(153, '2.0.206', '2022-01-04'),
(152, '2.0.204', '2021-12-21'),
(151, '2.0.202', '2021-11-25'),
(150, '1.4.200', '2019-10-14'),
(149, '1.4.199', '2019-03-13'),
(148, '1.4.198', '2019-02-22'),
(147, '1.4.197', '2018-03-18'),
(146, '1.4.196', '2017-06-10'),
(145, '1.4.195', '2017-04-23'),
(144, '1.4.194', '2017-03-10'),
(143, '1.4.193', '2016-10-31'),
(142, '1.4.192', '2016-05-26'),
(141, '1.4.191', '2016-01-21');

CREATE TABLE CHANNEL(TITLE VARCHAR, LINK VARCHAR, DESC VARCHAR,
    LANGUAGE VARCHAR, PUB TIMESTAMP, LAST TIMESTAMP, AUTHOR VARCHAR);

INSERT INTO CHANNEL VALUES('H2 Database Engine' ,
    'https://h2database.com/', 'H2 Database Engine', 'en-us', LOCALTIMESTAMP, LOCALTIMESTAMP, 'Thomas Mueller');

CREATE VIEW ITEM AS
SELECT ID, 'New version available: ' || VERSION || ' (' || CREATED || ')' TITLE,
CAST((CREATED || ' 12:00:00') AS TIMESTAMP) ISSUED,
$$A new version of H2 is available for
<a href="https://h2database.com">download</a>.
(You may have to click 'Refresh').
<br />
For details, see the
<a href="https://h2database.com/html/changelog.html">change log</a>.
$$ AS DESC FROM VERSION;

SELECT 'newsfeed-rss.xml' FILE,
    XMLSTARTDOC() ||
    XMLNODE('rss', XMLATTR('version', '2.0'),
        XMLNODE('channel', NULL,
            XMLNODE('title', NULL, C.TITLE) ||
            XMLNODE('link', NULL, C.LINK) ||
            XMLNODE('description', NULL, C.DESC) ||
            XMLNODE('language', NULL, C.LANGUAGE) ||
            XMLNODE('pubDate', NULL, FORMATDATETIME(C.PUB, 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')) ||
            XMLNODE('lastBuildDate', NULL, FORMATDATETIME(C.LAST, 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')) ||
            GROUP_CONCAT(
                XMLNODE('item', NULL,
                    XMLNODE('title', NULL, I.TITLE) ||
                    XMLNODE('link', NULL, C.LINK) ||
                    XMLNODE('description', NULL, XMLCDATA(I.TITLE))
                )
            ORDER BY I.ID DESC SEPARATOR '')
        )
    ) CONTENT
FROM CHANNEL C, ITEM I