Code Scratchpad: Queries: Attachment

From iDempiere en
Jump to navigation Jump to search

NF1.0_Storage_Providers store attachment file to folder and store paths into xml push to BINARYDATA of table AD_Attachment. below query run on oracle, parse data of BINARYDATA to row. so you can get list of paths of attachments

SELECT  attachInfo.filePath, attachInfo.fileName
FROM AD_Attachment x, 
  XMLTABLE('attachments/entry' 
      PASSING 
          CASE WHEN BINARYDATA IS NULL THEN 
              XMLTYPE.createXML('<?xml version="1.0" encoding="utf-8"?><attachments></attachments>') 
            ELSE 
              XMLTYPE.createXML(BINARYDATA , 871, null) 
          END 
      COLUMNS filePath varchar2(4000) PATH '@file',
              fileName varchar2(4000) PATH '@name') attachInfo
WHERE title = 'xml'