Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2017
    Posts
    12

    Answered: How to parse xml and get specific element using SQL in db2 with verion DSN11015 datab

    I have a table XML_INTERFACE with column XML_FILE as XML type , XML file has the following format <CalculationResponse><OBook specVersion="0161" timestamp="2017-04-05T05:55:06" ONumber="16191245" source="SOURCE" FNumber="00018956452345"><BrickItems><BrickItem ----
    </BrickItems></OBook></CalculationResponse>

    I want to extract specverion, ONumber, source, timestamp and FNumber using SQL

    I tried using the below query in DBVisualizer but does not work it just returns blank spaces

    select xmlquery('$doc/CalculationResponse/OBook/ONumber'
    passing cast(XML_FILE as XML) as "doc") from XML_INTERFACE

    Please help me to extract the fields as soon as possible.
    Last edited by arunajs; 09-02-17 at 12:22.

  2. Best Answer
    Posted by mark.bb

    "Your 'SAMPLE XML3' doesn't have specVersion,ONumber,sOurce,timestamp fields and has completely different structure.
    How do you want to process such XMLs?

    The 1-st 2 XMLs can be parsed with the same query:
    Code:
    select t.*
    from 
      XML_INTERFACE i
    , xmltable ('$D/CalculationResponse/OBook' 
      passing i.XML_FILE AS "D" COLUMNS 
      SPECVERSION char(4)     path '@specVersion'
    , ONUMBER     int         path '@ONumber'
    , SOURCE      varchar(10) path '@source'
    , TIMESTAMP   timestamp   path '@timestamp'
    , FNUMBER     char(14)    path '@FNumber'
    ) t;
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Try this:
    Code:
    select t.*
    from (
    select xmlparse (document '
    <CalculationResponse>
     <OBook specVersion="0161" timestamp="2017-04-05T05:55:06" ONumber="16191245" source="SOURCE" FNumber="00018956452345">
       <BrickItems>
        <BrickItem>Some Item</BrickItem>
       </BrickItems>
     </OBook>
    </CalculationResponse>
    ') as XML_FILE
    from sysibm.sysdummy1
    ) i
    , xmltable ('$D/CalculationResponse/OBook' 
      passing i.XML_FILE AS "D" COLUMNS 
      SPECVERSION char(4)     path '@specVersion'
    , ONUMBER     int         path '@ONumber'
    , SOURCE      varchar(10) path '@source'
    , TIMESTAMP   timestamp   path '@timestamp'
    , FNUMBER     char(14)    path '@FNumber'
    ) t;
    Regards,
    Mark.

  4. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Provide the DDL for your tables and 'INSERT INTO XML_INTERFACE' statements with your sample XML data.
    Regards,
    Mark.

  5. #4
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark

    I am using DB2 Visualizer 9.5.4 I dont know how to get the DDL . But I can give sample XML as below

    SAMPLE XML1:

    <CalculationResponse><OBook specVersion="0161" timestamp="2017-04-05T05:55:06" ONumber="16191245" source="SOURCE1" FNumber="00014606191245"><BrickItems></BrickItem></BrickItems></OBook></CalculationResponse>
    Last edited by arunajs; 09-04-17 at 12:30.

  6. #5
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark,

    Thanks alot for the response.
    This works perfect only for that XML which I posted but in have many similar XML_FILE's in the table XML_INTERFACE I have to pick specVersion,ONumber,sOurce,timestamp for all the XML files along with the xmlfile.

    I am new to dB2 I never used before .

    Let me explain in detail for the all error messages in LINE_ERROR i need to get distinct ERROR seq num (FK_ERR_SEQ_NUMBER) and using FK_ERR_SEQ_NUMBER i.e SEQ NUM check in ERROR table if exists then using the ORDER in the ERROR table I have to check XML_INTERFACE table for the ORDER and parse the fields and insert into NOT_PROCESS table.

    I need generic query so it works for all the rows.
    Appreciating your help in writing the query.
    Last edited by arunajs; 09-04-17 at 12:31.

  7. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Your 'SAMPLE XML3' doesn't have specVersion,ONumber,sOurce,timestamp fields and has completely different structure.
    How do you want to process such XMLs?

    The 1-st 2 XMLs can be parsed with the same query:
    Code:
    select t.*
    from 
      XML_INTERFACE i
    , xmltable ('$D/CalculationResponse/OBook' 
      passing i.XML_FILE AS "D" COLUMNS 
      SPECVERSION char(4)     path '@specVersion'
    , ONUMBER     int         path '@ONumber'
    , SOURCE      varchar(10) path '@source'
    , TIMESTAMP   timestamp   path '@timestamp'
    , FNUMBER     char(14)    path '@FNumber'
    ) t;
    Regards,
    Mark.

  8. #7
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark,

    Thanks for the help it works perfect for the Sample XML1 .
    Actually we get 3 types of XML in the Sample XML3 in the General tag <General OOrderNumber="15049368" SpecVersion="5" ...... />
    we have the 2 fields only thing is we have schema in the SAMPLE XML3 ....

    Thanks a lot for the help ......

    For each type of XML I have to write different queries to handle so for Sample XML3 I have to add the schema in the query along with XPATH I just gave namespace with the path but it does not work.So please let me know how can i add namespace for SAmple XML3?

    Also using this how to join the tables ERROR and LINE_ERROR table
    select t.*
    from
    XML_INTERFACE i
    , xmltable ('$D/CalculationResponse/OBook'
    passing i.XML_FILE AS "D" COLUMNS
    SPECVERSION char(4) path '@specVersion'
    , ONUMBER int path '@ONumber'
    , SOURCE varchar(10) path '@source'
    , TIMESTAMP timestamp path '@timestamp'
    , FNUMBER char(14) path '@FNumber'
    ) t join ERROR as orderror
    on orderror.ORDER=i.ORDER and ordlerror.SOURCE= 'SOURCE1' and i.SOURCE='SOURCE1'
    join LINE_ERROR as ordlerror
    on ordlerror.FK_ORD_ERR_SEQ_NUMBER=orderror.SEQUENCE_ NUMBER

    I tried to join but is not working please correct me.
    Last edited by arunajs; 09-03-17 at 13:36.

  9. #8
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    First of all: please, don't print the statements like 'I tried to join but is not working, please correct me'.
    If something doesn't work, then specify, what doesn't work exactly, and what error code and message you get. Otherwise the only suggestion you can get is 'change something'
    Quote Originally Posted by arunajs View Post
    For each type of XML I have to write different queries to handle so for Sample XML3 I have to add the schema in the query along with XPATH I just gave namespace with the path but it does not work.So please let me know how can i add namespace for SAmple XML3?
    Try this:
    Code:
    select 
      coalesce(t1.specversion, t2.specversion) as specversion
    , coalesce(t1.onumber, t2.onumber) as onumber
    , t1.source, t1.timestamp, t1.fnumber
    from 
      XML_INTERFACE i
    left join xmltable ('$D/CalculationResponse/OBook' 
      passing i.XML_FILE AS "D" COLUMNS 
      SPECVERSION char(4)     path '@specVersion'
    , ONUMBER     int         path '@ONumber'
    , SOURCE      varchar(10) path '@source'
    , TIMESTAMP   timestamp   path '@timestamp'
    , FNUMBER     char(14)    path '@FNumber'
    ) t1 on 1=1
    left join xmltable(XMLNAMESPACES(DEFAULT 'http://temp.org/ValidationResponseToOM.xsd'),
      '$D/IOrderAccount/Orders/Order/General' 
      passing i.XML_FILE AS "D" COLUMNS 
      SPECVERSION char(4)     path '@SpecVersion'
    , ONUMBER     int         path '@OOrderNumber'
    ) t2 on 1=1
    ;
    Regards,
    Mark.

  10. #9
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark,

    Ok sure here after I will do as suggested. This is first time I am posting query in Forums thanks for suggestion and your time.

    The query works perfect which you have given and thanks for your help.

    Requirement is I need to get disticnt row from Error table with LINE_ERROR.FK_ORD_ERR_SEQ_NUMBER=ERROR.SEQUENCE_NU MBER .
    then using ORDER and SOURCE from ERROR i need to query XML_INTERFACE table and get the orderror.ORDER_ID,ORDER_ID.SPECIFICATION_VERSION,x io.SOURCE,xio.XML_FILE for the latest creation date.
    Last edited by arunajs; 09-04-17 at 12:33.

  11. #10
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    What's the result of the following query:
    Code:
    select 
      case colno when 0 then 'create table "'||tbname||'" (' else '' end
    ||case colno when 0 then '' else ',' end ||' "'||name||'" '||coltype
    ||
    case when coltype like '%CHAR%' or coltype like '%LOB%' or coltype like '%DEC%' 
    then ' ('||char(length)||')' else '' 
    end col
    , scale
    from sysibm.syscolumns
    where tbname in ('XML_INTERFACE', 'ERROR', 'LINE_ERROR', 'NOT_PROCESS')
    order by tbname, colno
    Regards,
    Mark.

  12. #11
    Join Date
    Sep 2017
    Posts
    12
    No error . No result set too I got below message on executing the query.
    Physical database connection acquired for: RewriteXDEV
    13:59:15 [SELECT - 0 rows, 0.225 secs] Empty result set fetched
    ... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.225/0.000 sec [1 successful, 0 errors]

    In the meantime I tried this below one query but unable to join LINE_ERROR ordlerror on ordlerror.FK_ORD_ERR_SEQ_NUMBER=orderror.SEQUENCE_ NUMBER I need to join now ERROR and LINE_ERROR table with SEQUENCE NUMBER and get unique number

    select orderror.ORDER,orderror.SPECIFICATION_VERSION,x.so urce0 ,x.XML_FILE
    from (select ORDER_ID,SOURCE0,max (CREATION_DATE) latestdate from XML_INTERFACE group by ORDER,SOURCE0) xio
    join XML_INTERFACE x on xio.ORDER = x.ORDER and x.CREATION_DATE = latestdate
    join ERROR orderror on xio.ORDER =orderror.ORDER

  13. #12
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark,

    I tried the below query it returns row with least date I want the query to return max date .

    Suppose we have dates .....
    CREATION DATE.
    2015-12-26 07:47:30
    2015-12-04 08:27:35
    2015-12-01 09:47:00
    2015-09-17 04:40:28
    2015-09-04 09:42:17
    2015-08-27 13:25:16

    Below query returned rows with date "2015-08-27 13:25:16" which Is wrong I want the first one "2015-12-26 07:47:30" to be returned.

    select orderror.ORDER,orderror.SPECIFICATION_VERSION,x.so urce0 ,x.XML_FILE,x.CREATION_DATE
    from (select ORDER,SOURCE0,max (CREATION_DATE) latestdate from XML_INTERFACE group by ORDER,SOURCE0) xio ,
    (select distinct(FK_ORD_ERR_SEQ_NUMBER) from LINE_ERROR) as lineerror
    join XML_INTERFACE x on xio.ORDER = x.ORDER and x.CREATION_DATE = latestdate
    join ERROR orderror on xio.ORDER_ID =orderror.ORDER_ID and lineerror.FK_ORD_ERR_SEQ_NUMBER=orderror.SEQUENCE_ NUMBER

  14. #13
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Aruna,

    You have a huge number of discrepancies between your table column names and sql queries. I even don't want to enumerate them all.
    So, please:
    - provide the full 'CREATE TABLE' statement for each participating table; if you can't do it, ask somebody near you
    - specify exactly, how each pair of tables related to each other exactly (join columns)
    - it's good to provide some sample data for each column (INSERT INTO statements)

    I'm afraid, that nobody here will do these steps for you...
    Regards,
    Mark.

  15. #14
    Join Date
    Sep 2017
    Posts
    12
    Hi Mark,

    I am using DB visualizer here I dont have option to export insert statements.
    Thanks for great help and amazing support Mark.
    Last edited by arunajs; 09-04-17 at 12:28.

Tags for this Thread

Posting Permissions

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