Query XML from a Clob and join it with a table (DB2)

Question

I have an xml like this stored in a table:

<xml>
     <attribute>name</attribute>
     <value>this is my xml</value>
     <logLocation>/user/apps/myLogFolder</logLocation>
     <engines>
        <engine>
            <engineName>Engine1</engineName>
            <engineLocation>$HOME/apps/engines</engineLocation>
            <engineVersion>3.1416</engineVersion>
        </engine>
        <engine>
            <engineName>Engine2</engineName>
            <engineLocation>$HOME/apps/engines/backup</engineLocation>
            <engineVersion>3b</engineVersion>
        </engine>
    </engines>
    <connections>
        <connection>
        <jdbc>jdbc:db2://127.0.0.1:50000/localdb</jdbc>
        <user>dbuser</user>
        <password>{}</password>
        </connection>
    </connections>
</xml>

The table has the following structure:

id         | xmlText        |
(integer)  | (clob)         |
---------- | -------------- |
1          | [clob]         |
2          | [clob]         |
---------- | -------------- |

Said xmls are stored in each clob column.

What I am trying to do is to search for a value and then join it with a regular table.

The query I am trying to achieve is something like this:

select
    t1.*,
    xmlValues.logLocation
from
    schema.table t1,
    (
        XMLTABLE
        (
        SELECT
            id,
            xmlserialize(xmltext AS VARCHAR(3000)) as xmlValues
        FROM
            schema.myXMLTable
        WHERE
            id = t1.id
        FETCH FIRST ROW ONLY
        )
    ) xmlValues
where
    t1.id = 1;

Since I this query will be used in some other code, it won't have access to the DB2 console.

How can I achieve it?

Thanks.

Answer 1

Thanks to this:

Extract data from XML Clob using SQL from db2

I was able to get this:

select
    myJoinTable.objectName,
    myJoinTable.objectType,
    xmlTableValues.jdbcString,
    xmlTableValues.logPath
from
    myJoinTable,
    (
        SELECT
            tb1.id as id
            xmlField.jdbcString as jdbcString,
            xmlField.logPath as logPath
        FROM myTable tb1,
            XMLTABLE(
                '$xmlDoc/xml/connections' PASSING XMLPARSE(DOCUMENT tb1.xmlText) AS "xmlDoc"
                COLUMNS 
                jdbcString varchar(200) PATH 'connection[1]/jdbc',
                logPath varchar(500) PATH '../logLocation'
            ) AS xmlField
        ) xmlValuesTable
    ) xmlTableValues
where
    myJoinTable.objectID = X
    and
        myJoinTable.objectID = xmlTableValues.id;

Which allows me to query what I need.

Thanks.