Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    41

    Unanswered: CLOB with data in xml format - parse idea's

    Hello All,

    Just looking for some advice here.

    I have a clob field that contains a string of XML Text. It appears to be just a dump of data
    I need to parse certains elements of the string and store them in another field of the same table.

    I don't really know what is the best approach here.
    I was looking at CTE's . I see it can also be done in Java but was hoping to stay within what db2 has available as I am not a great coder.

    Sample data from the field is
    <tns:dMSStatusMsg xmlns="http://www.tmdd.org/3/messages" xmlns:tns="http://www.tmdd.org/3/dialogs" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tmdd.org/3/dialogs ../Schema/eTMDD.xsd ">
    <dms-status-item>
    <DmsStatusHeader>
    <DeviceId>1168</DeviceId>
    <DeviceStatus>operational</DeviceStatus>
    <DeviceUrl></DeviceUrl>
    <LastCommTime>
    <date>20130812</date>
    <time>160317</time>
    <offset>-0400</offset>
    </LastCommTime>
    </DmsStatusHeader>
    <DmsDiagnosticMessage>local mode is False</DmsDiagnosticMessage>
    <DmsCurrentMessage-Text>W1ROK.. Blank</DmsCurrentMessage-Text>
    <DmsCurrentMessage-Name>W1ROK.. Blank</DmsCurrentMessage-Name>
    <DmsCurrentMessage-ImageFormat>png</DmsCurrentMessage-ImageFormat>
    <DmsMessageSourceMode>8</DmsMessageSourceMode>
    </dms-status-item>
    </tns:dMSStatusMsg>

    I need information from <DmsDiagnosticMessage>

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by alexandra123 View Post
    Hello All,

    Just looking for some advice here.

    I have a clob field that contains a string of XML Text. It appears to be just a dump of data
    I need to parse certains elements of the string and store them in another field of the same table.

    I don't really know what is the best approach here.
    I was looking at CTE's . I see it can also be done in Java but was hoping to stay within what db2 has available as I am not a great coder.

    Sample data from the field is



    I need information from <DmsDiagnosticMessage>

    Have you tried xmlparse?

    with t(c) as ( values cast('<tns:dMSStatusMsg xmlns="http://www.tmdd.org/3/messages" xmlns:tns="http://www.tmdd.org/3/dialogs" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tmdd.org/3/dialogs ../Schema/eTMDD.xsd ">
    <dms-status-item>
    <DmsStatusHeader>
    <DeviceId>1168</DeviceId>
    <DeviceStatus>operational</DeviceStatus>
    <DeviceUrl></DeviceUrl>
    <LastCommTime>
    <date>20130812</date>
    <time>160317</time>
    <offset>-0400</offset>
    </LastCommTime>
    </DmsStatusHeader>
    <DmsDiagnosticMessage>local mode is False</DmsDiagnosticMessage>
    <DmsCurrentMessage-Text>W1ROK.. Blank</DmsCurrentMessage-Text>
    <DmsCurrentMessage-Name>W1ROK.. Blank</DmsCurrentMessage-Name>
    <DmsCurrentMessage-ImageFormat>png</DmsCurrentMessage-ImageFormat>
    <DmsMessageSourceMode>8</DmsMessageSourceMode>
    </dms-status-item>
    </tns:dMSStatusMsg> ' as clob))

    select xmlparse(document c) from t;
    --
    Lennart

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    hi lennart,

    the column is dynamic so I dont think that will work. ie the values within each of the tags will change. Do you have any other suggestions ?

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by alexandra123 View Post
    hi lennart,

    the column is dynamic so I dont think that will work. ie the values within each of the tags will change. Do you have any other suggestions ?
    I don't understand, assuming your table is named t, and the clob column named c, why can't you convert the clob to an xml document, and do the usual xmlparsing from there? Say:

    Code:
    select xmlquery('$d/*:dMSStatusMsg/*:dms-status-item/*:DmsStatusHeader/*:DeviceId' PASSING (xmlparse(document c)) as "d" ) from t;
    Last edited by lelle12; 08-27-13 at 11:15. Reason: removed cte
    --
    Lennart

  5. #5
    Join Date
    Aug 2012
    Posts
    41
    Thanks to the responses and support on this . It was a tough one for me. the xmlquery did not work for me as it was not strict.

    I got it to work using the below in db2
    Code:
    POSSTR("COMMENTTEXT", '<DmsDiagnosticMessage>') "start_DmsDiagnosticMessage" , 
    POSSTR("COMMENTTEXT", '</DmsDiagnosticMessage>') "end_DmsDiagnosticMessage" , 
    21 + POSSTR("COMMENTTEXT", '<DmsDiagnosticMessage>') "start_position" ,
    23 + POSSTR("COMMENTTEXT", '</DmsDiagnosticMessage>') "end_position" , 
    POSSTR("COMMENTTEXT", '</DmsDiagnosticMessage>') - (21 + POSSTR("COMMENTTEXT", '<DmsDiagnosticMessage>')) "string_length" , 
    right(upper (substr("COMMENTTEXT", 1, POSSTR("COMMENTTEXT", '</DmsDiagnosticMessage>'))), POSSTR("COMMENTTEXT", '</DmsDiagnosticMessage>') - (21 + POSSTR("COMMENTTEXT", '<DmsDiagnosticMessage>'))) "TEXT" ,
    Last edited by alexandra123; 09-02-13 at 19:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •