Hi All,
I need help with the syntax of xmltable, I have a stmf with an xml document like this:
Now with a runsql script I try this:
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.
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>
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 ;
Many thanks for your help.
Comment