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

    Unanswered: extract xml data from clob ?

    Hello,

    I am looking for some advice on how to extract xml data from a CLOB ?

    here is the content within the clob

    Code:
    UserName : , DeviceId : , CommStatus : ,* XML : <?xml version="1.0" encoding="UTF-8"?>
    <tns:dMSStatusMsg xmlns:p="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></DeviceId>
          <DeviceStatus></DeviceStatus>
          <DeviceUrl></DeviceUrl>
          <LastCommTime>
            <date></date>
            <time></time>
            <offset></offset>
          </LastCommTime>
        </DmsStatusHeader>
        <DmsDiagnosticMessage></DmsDiagnosticMessage>
        <DmsCurrentMessage-Text></DmsCurrentMessage-Text>
        <DmsCurrentMessage-Name></DmsCurrentMessage-Name>
        <DmsCurrentMessage-Image></DmsCurrentMessage-Image>
        <DmsCurrentMessage-ImageFormat></DmsCurrentMessage-ImageFormat>
        <DmsMessageNumber></DmsMessageNumber>
        <DmsBadPixelPercentage></DmsBadPixelPercentage>
        <DmsMessageSourceMode></DmsMessageSourceMode>
        <DmsMessagePriority></DmsMessagePriority>
      </dms-status-item>

    I tried different queries but I am not able to extract what i need. I am looking to extract everything within the node
    Code:
    <DmsCurrentMessage-Image></DmsCurrentMessage-Image>

    samples of what I tried already using any of the nodes within the xml


    SELECT XMLQUERY ('$d/TABLE.COLUMN/dms-status-item/DmsMessageNumber' PASSING DmsMessageNumber as "d" )
    FROM TABLE WHERE MESSAGEID = 326925

    Code:
    XQUERY
      db2-fn:xmlcolumn ('dms-status-item')/dms-status-item/DmsMessageNumber
    Code:
    xquery db2-fn:sqlquery("select commenttext from TABLE where MESSAGEID = 326925")/dms-status-item/DmsMessageNumber
    Code:
    SELECT XMLPARSE (DOCUMENT CAST (COMMENTTEXT AS BLOB) PRESERVE WHITESPACE) FROM TABLE

    Code:
    xquery
    declare default element namespace = "http://www.w3.org/2001/XMLSchema-instance";
    for $c in db2-fn:xmlcolumn("TABLE.COLUMN")/dms-status-item 
    return $c/DmsMessageNumber

    The value within the clob is large so I need to parse it out as a clob and not as a varchar

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select xmlcast(xmlquery('$d/dms-status-item/DmsCurrentMessage-Image' passing xmlparse(document v.x) as "d") as varchar(20))
    from table (values 
      clob('<dms-status-item>
        <DmsStatusHeader>
          <DeviceId></DeviceId>
          <DeviceStatus></DeviceStatus>
          <DeviceUrl></DeviceUrl>
          <LastCommTime>
            <date></date>
            <time></time>
            <offset></offset>
          </LastCommTime>
        </DmsStatusHeader>
        <DmsDiagnosticMessage></DmsDiagnosticMessage>
        <DmsCurrentMessage-Text></DmsCurrentMessage-Text>
        <DmsCurrentMessage-Name></DmsCurrentMessage-Name>
        <DmsCurrentMessage-Image>My text</DmsCurrentMessage-Image>
        <DmsCurrentMessage-ImageFormat></DmsCurrentMessage-ImageFormat>
        <DmsMessageNumber></DmsMessageNumber>
        <DmsBadPixelPercentage></DmsBadPixelPercentage>
        <DmsMessageSourceMode></DmsMessageSourceMode>
        <DmsMessagePriority></DmsMessagePriority>
      </dms-status-item>')
    ) v(x)
    If you really have some binary data in the DmsCurrentMessage-Image tag, we need to know how you encode this data.
    Regards,
    Mark.

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    Hello Mark,

    Thanks for the response.

    If I run your query as is - I get back the "my text"


    If I try to update your query to point to the tablename.column name the query will not work

    ref to the from clause

    Code:
    select xmlcast(xmlquery('$d/dms-status-item/DmsCurrentMessage-Image' passing xmlparse(document v.x) as "d") as varchar(20))
    from STAGE."M_MESSAGE".text (values
    Stage is the schema name
    m_message is the table name
    text is the column name

    Error

    Code:
    SQL0104N  An unexpected token "(" was found following "MESSAGE".text".  Expected tokens may include:  "JOIN
    Is their something simple I am doing wrong ?


    Also - when I show the images I wrap them around html tags with decoding of base64 as below

    <img src="data:image/png;base64,[IMAGE]"/>

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If you have your imaged encoded as base64, then casting to blob should work:
    Code:
    select xmlcast(xmlquery('$d/dms-status-item/DmsCurrentMessage-Image' passing xmlparse(document v.text) as "d") as blob(1m))
    from STAGE.M_MESSAGE v
    Regards,
    Mark.

Posting Permissions

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