XML -> GUI_DOWNLOAD Version Column

[Former Member]

When I run the program below and then open the resulting Excel file, I have two problems:

1. The first column is not the MANDT column as I would expect.  Rather the first column is labeled "Version" and has the number 1 in each row.  I would like to remove this column.
2. When I open the file I get a popup box with title "Open XML" that provides 3 options of how I would like to open the file.  If it is possible I would like to avoid this popup.  The user will always open "As an XML Table", so this is not critical, but it would be nice to avoid this step if possible.

TYPES: xmlline(1024) TYPE x.

DATA: lt_t005 TYPE STANDARD TABLE OF t005.

DATA: lt_xml  TYPE STANDARD TABLE OF xmlline.

SELECT * FROM t005 INTO TABLE lt_t005 UP TO 100 ROWS.

CALL TRANSFORMATION id

   SOURCE data_node = lt_t005

   RESULT XML lt_xml.

CALL METHOD cl_gui_frontend_services=>gui_download

   EXPORTING

     filename = 'C:\Users\Mike\Desktop\test.xls'

     filetype = 'BIN'

   CHANGING

     data_tab = lt_xml.

Thanks,

Mike

[Former Member]

When I run the program below and then open the resulting Excel file, I have two problems:

1. The first column is not the MANDT column as I would expect.  Rather the first column is labeled "Version" and has the number 1 in each row.  I would like to remove this column.
2. When I open the file I get a popup box with title "Open XML" that provides 3 options of how I would like to open the file.  If it is possible I would like to avoid this popup.  The user will always open "As an XML Table", so this is not critical, but it would be nice to avoid this step if possible.

TYPES: xmlline(1024) TYPE x.

DATA: lt_t005 TYPE STANDARD TABLE OF t005.

DATA: lt_xml  TYPE STANDARD TABLE OF xmlline.

SELECT * FROM t005 INTO TABLE lt_t005 UP TO 100 ROWS.

CALL TRANSFORMATION id

   SOURCE data_node = lt_t005

   RESULT XML lt_xml.

CALL METHOD cl_gui_frontend_services=>gui_download

   EXPORTING

     filename = 'C:\Users\Mike\Desktop\test.xls'

     filetype = 'BIN'

   CHANGING

     data_tab = lt_xml.

Thanks,

Mike

[Former Member]

Hi.

As for first point, you MANDT is part of T005 table so it will be send by transformation ID always, a workaround is to have a type with all the fields of T005 exluding the MANDT one and use it as the table type for the internal table LT_T005, the do the select query as usual.

Regards.

[Former Member]

Yes, I understand that about MANDT.  That field clearly should be the first field listed.  But for some reason this new VERSION field, generated from version = 1.0 tag in the XML (I guess), is coming up first.  The VERSION field is the one I need to get rid of.

Mike

[naimesh_patel]

This is how the generated XML looks.  This highlighted version is treated "common" by excel XML table and would be thus present to all the columns.

<?xml version="1.0" encoding="utf-16"?>
  <asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">
    <asx:values>
       <DATA_NODE>
          <T005>
             <MANDT>100</MANDT>
             <LAND1>AD</LAND1>
...
          </T005>
       </DATA_NODE>
    </asx:values>
  <asx:abap>

Either you can get the XML response into XML object (IF_IXML_DOCUMENT) or get into the string and replace the node.

Or you can create your own little transformation and omit this version.

Regards,
Naimesh Patel

[Former Member]

Perfect. Thanks Naimesh, that works.  In case anyone comes along with the same problem later, using what Naimesh told me I:

1. moved the XML to a string using  cl_xml_document-> parse_xstring
2. replace first occurrence of '<asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">' in lv_xml_string with '<asx:abap xmlns:asx="http://www.sap.com/abapxml">'
3. moved the string back to XML using  parse_string

And it worked perfectly - No Version column in my resulting Excel file.

[naimesh_patel]

I simplified your approach by receiving the XML result in the string, instead of the xstring. So, no need to convert from xstring to string for manipulation. Also used the method EXPORT_TO_FILE of class CL_XML_DOCUMENT.

DATA: lt_t005 TYPE STANDARD TABLE OF t005.
DATA: lv_xml  TYPE string.
DATA: lo_xml_doc TYPE REF TO cl_xml_document.

SELECT * FROM t005 INTO TABLE lt_t005 UP TO 100 ROWS.

CALL TRANSFORMATION id
   SOURCE data_node = lt_t005
   RESULT XML lv_xml.

REPLACE FIRST OCCURRENCE OF
    '<asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">'
  IN lv_xml
  WITH '<asx:abap xmlns:asx="http://www.sap.com/abapxml">'.

CREATE OBJECT lo_xml_doc.
lo_xml_doc->parse_string( lv_xml ).
lo_xml_doc->export_to_file( 'c:\temp\t005_new_1.xls' ).

Regards,
Naimesh Patel