ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Help with Xmltable

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with Xmltable

    Hi All,
    I need help with the syntax of xmltable, I have a stmf with an xml document like this:
    Code:
    <NRRHubResponseFile xmlns="http://nexregreporting.com/NRRHubResponseFile.xsd" Version="1">
    <Header>
    <ResponseFileName>
    bbb.xml
    </ResponseFileName>
    <ClientIdentifier>aaaa</ClientIdentifier>
    <ClientSource>
    815600A4DA95C74BB576_MIFID2_20180123_115355_Direct a_simpa_0001.csv
    </ClientSource>
    <Received>2018-01-23T11:08:48.0000000</Received>
    <TotalRecordCount>13192</TotalRecordCount>
    <TotalPassedValidation>357</TotalPassedValidation>
    <TotalFailedValidation>48</TotalFailedValidation>
    <SourceResultCode>100</SourceResultCode>
    </Header>
    <Record NexID="81d83111-87af-44b0-adce-08d56251b027" UTI="" TRN="2018010300321320488217000001" OriginalFileRowNumber="13127" ActionType="NEWT"ExecutionTimestamp="2018-01-03T20:32:05.000000Z" ValuationTimestamp="" RecordResultCode="100">
    <Error ErrorDescription="Buyer MIC XOFF is not valid for the trade date" ErrorSource="REP"/>
    </Record>
    <Record NexID="6c4ce9bc-d4b6-4a53-8cc9-08d56251b027" UTI="" TRN="2018010300321382388267000001" OriginalFileRowNumber="13156" ActionType="NEWT"ExecutionTimestamp="2018-01-03T20:38:23.000000Z" ValuationTimestamp="" RecordResultCode="500"/>
    <Record NexID="2f11cb1a-17fd-4eb0-9369-08d56251b027" UTI="" TRN="2018010300321355288246000003" OriginalFileRowNumber="13157" ActionType="NEWT"ExecutionTimestamp="2018-01-03T20:38:26.000000Z" ValuationTimestamp="" RecordResultCode="100">
    <Error ErrorDescription="Buyer MIC XOFF is not valid for the trade date" ErrorSource="REP"/>
    </Record>
    </NRRHubResponseFile>
    Now with a runsql script I try this:

    Code:
    Select record.*
    from XmlTable(XMLNAMESPACES(DEFAULT 'http://nexregreporting.com/NRRHubResponseFile.xsd') ,
    '$a/NRRHubResponseFile/Record'
    passing xmlparse(DOCUMENT get_xml_file('a.xml') )
    as "a"
    Columns
    NexId varchar(50) path '@NexID',
    Uti varchar(32) path '@UTI',
    Trn varchar(52) path '@TRN',
    OriginalFileRowNumber Int path '@OriginalFileRowNumber',
    ActionType Char(5) path '@ActionType',
    ExecutionTimestamp VarChar(30) path '@ExecutionTimestamp',
    ValuationTimestamp VarChar(30) path '@ValuationTimestamp',
    RecordResultCode Varchar(10) path '@RecordResultCode',
    ErrorDescription Varchar(150) path '/Error/@ErrorDescription',
    ErrorSource VarChar(10) path '/Error/@ErrorSource'
    ) as record
    ;
    My problem is than I can't retreive the value of the ErrorDescription and ErrorSource, in the result of my select the errorDescription and errorsource are always null, What am I doing wrong?


    Many thanks for your help.

  • #2
    Hi,

    my collegue find the error, it was the / in the path of the errorDescription and errorsource.
    path 'Error/@ErrorSource' My xml have about 20000 rows, the select is very very slow...

    Comment

    Working...
    X