CODEWITHSUNDEEP
sqldb2
J. Doe
J. Doe

Reputation: 21

Select on CLOB XML DB2

I have this data as CLOB field in DB2. I am converting the data to char using cast: 

SELECT CAST(CLOBColumn as VARCHAR(32000))
FROM Schema.MyTable;

Here is how the result XML comes out from the above: 

<TreeList TreeNo="ABC">
    <Tree ErrorCode="INVALID_TREE" ErrorDescription="Tree doesn’t exist." TreeID="123456"/>
    <Tree ErrorCode="INVALID_TREE" ErrorDescription="Tree doesn’t exist." TreeID="1234567"/>
</TreeList>

And this is how I expect my output 

|TreeNo | TreeID  |   ErrorCode  | ErrorDescription
|ABC    | 123456  | INVALID_TREE | Tree doesn’t exist
|ABC    | 1234567 | INVALID_TREE | Tree doesn’t exist

How do I achieve this?

Upvotes: 1

Views: 2984

Answers (1)

data_henrik
data_henrik

Reputation: 17118

You need to use the XMLTABLE function which allows to map XML data to a table. You can pass in XML-typed data and it works if you directly parse the CLOB to XML. The SELECT would look like the following (you get the idea):

SELECT x.*
FROM schema.mytable, XMLTABLE(
    '$CLOBColumn/TreeList'
    COLUMNS 
    TreeNo VARCHAR(10) PATH '@TreeNo',
    TreeID INT PATH 'Tree[@TreeID]',
    ...
) AS x
;

Upvotes: 3

Related Questions