topic Re: Convert XML to SAP internal table in Application Development and Automation Discussions

Convert XML to SAP internal table

Former Member — Tue, 28 Dec 2010 11:02:49 GMT

Hi gurus,

My program is suppose to get current exchange rate file from website and upload it in SAP.

by using FM 'HTTP_GET', im getting the data in XML format.My problem is..i have tried to use transformation but failed to

map it against XML data element..and i have tried to use CALL FUNCTION 'SCMS_TEXT_TO_XSTRING' and then CALL FUNCTION 'SMUM_XML_PARSE' but it returns initial value with error ''line 0 col 12-unexpected symbol: '>'' .

Below is an excerpt of my XML file:

..<xml version>

..<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

.. xmlns:o="urn:schemas-microsoft-com:office:office"

.. xmlns:x="urn:schemas- microsoft-com:office:excel"

.. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

.. <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>10875</WindowHeight>........ <WindowWidth>13260</WindowWidth><WindowTopX>480</WindowTopX><WindowTopY>30</Wind

.. <Style ss:ID="Default" ss:Name="Normal">

.. <Alignment ss:Vertical="Bottom"/>

.. <Borders/>

.. <Font/>

.. <Interior/>

.. <NumberFormat/>

.. <Protection/>

.. </Style>

.. <Style ss:ID="BoldColumn">

.. <Font ss:FontName="Microsoft Sans Serif" x:Family="Swiss" ss:Bold="1"/>

.. <Worksheet ss:Name="Page1"><Table ss:StyleID="s21" > <Column ss:Width="130"/> <Column ss:Width="85"/> <Column .. ss:Width="85"/> <Column ss:Width="85"/> <Column ss:Widt

.. Cell><Cell ss:MergeAcross="1" ss:StyleID="BoldColumn"><Data ss:Type="String">Average Buying Rates</Data></Cell><Cell .. ss:MergeDown="1" ss:StyleID="BoldColumn"><Data ss:Type="Stri

.. ss:Type="Number">47.3732</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">47.5374</Data></Cell><Cell .. ss:StyleID="Decimal"><Data ss:Type="Number">48.2323</Data></Ce

.. " ><Data ss:Type="String">HONG KONG</Data></Cell><Cell ss:StyleID="StringLiteralCenter" ><Data .. .. ss:Type="String">HKD</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Numb

.. ss:Type="Number">22.8131</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">23.2595</Data></Cell>.. .. </Row><Row><Cell ss:StyleID="StringLiteral" ><Data ss:Type="String"

.. ss:StyleID="StringLiteralCenter" ><Data ss:Type="String">IDR</Data></Cell><Cell ss:StyleID="Decimal"><Data .. ss:Type="Number">3.0395</Data></Cell><Cell ss:StyleID="Decimal"><Data

.. ecimal"><Data ss:Type="Number">30.6424</Data></Cell></Row><Row><Cell ss:StyleID="StringLiteral" ><Data .. ss:Type="String">AUSTRALIA</Data></Cell><Cell ss:StyleID="StringLiteralCe

.. ell ss:StyleID="Decimal"><Data ss:Type="Number">0.3392</Data></Cell><Cell ss:StyleID="Decimal"><Data .. ss:Type="Number">0.3409</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Typ

.. StringLiteral" ><Data ss:Type="String">DENMARK</Data></Cell><Cell ss:StyleID="StringLiteralCenter" ><Data .. ss:Type="String">DKK</Data></Cell><Cell ss:StyleID="Decimal"><Data ss

.. ata ss:Type="Number">4.4550</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">4.6073</Data></Cell>.. </Row><Row></Row><Row></Row><Row><Cell ss:MergeAcross="4" ss:Style

.. s of 23 November 2010</Data></Cell></Row><Row></Row><Row><Cell ss:MergeAcross="4" .. ss:StyleID="BoldColumn_Left" ><Data ss:Type="String">2. Foreign Exchange Rates (THOMSON REUTE

.. ><Data ss:Type="String">ZAR</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">4.2735</Data></Cell>.. <Cell ss:StyleID="Decimal"><Data ss:Type="Number">4.3125</Data></

.. ="Decimal"><Data ss:Type="Number">106.5459</Data></Cell><Cell ss:StyleID="Decimal"><Data .. ss:Type="Number">107.5164</Data></Cell></Row><Row><Cell ss:StyleID="StringLiteral" ><Da

.. a></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">4.6916</Data></Cell></Row><Row><Cell .. ss:StyleID="StringLiteral" ><Data ss:Type="String">BANGLADESH</Data></Cell><Cell

.. r">0.0074</Data></Cell></Row><Row><Cell ss:StyleID="StringLiteral" ><Data ss:Type="String">KENYA</Data></Cell><Cell .. ss:StyleID="StringLiteralCenter" ><Data ss:Type="String">KE

.. ><Cell ss:StyleID="StringLiteral" ><Data ss:Type="String">VIET NAM</Data></Cell><Cell ss:StyleID="StringLiteralCenter" >.. <Data ss:Type="String">VND</Data></Cell><Cell ss:StyleI

.. A</Data></Cell><Cell ss:StyleID="StringLiteralCenter" ><Data ss:Type="String">LKR</Data></Cell><Cell .. .. ss:StyleID="Decimal"><Data ss:Type="Number">0.2686</Data></Cell><Cell ss:St

.. String">OMR</Data></Cell><Cell ss:StyleID="Decimal"><Data ss:Type="Number">77.7094</Data></Cell><Cell

Please help me to translate my XML file into SAP format...i need it fixed urgently..

Edited by: Guest101 on Dec 28, 2010 12:11 PM

Re: Convert XML to SAP internal table

Former Member — Wed, 29 Dec 2010 09:58:28 GMT

anyone ??? please...

Re: Convert XML to SAP internal table

Former Member — Wed, 29 Dec 2010 11:08:24 GMT

call method cl_gui_frontend_services=>gui_download

Exporting

bin_filesize = l_xml_size

filename = filename

filetype = 'BIN'

write_lf = 'X'

Changing

data_tab = l_xml_table

Exceptions

Others = 24 .

if sy-subrc <> 0 .

Message Id sy-msgid Type sy-msgty Number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

endif.

Re: Convert XML to SAP internal table

Former Member — Wed, 29 Dec 2010 11:11:26 GMT

u can try this function cl_gui_backend_services=>gui_upload it will work for to upload data in sap from xml file.

Re: Convert XML to SAP internal table

Former Member — Wed, 29 Dec 2010 12:26:01 GMT

It has 2 part one is source code and another is the XML file

create an XML file with the data provided in the last 2.

then place it in a path mention the path in the below source.

1. Complete code for converting XML to internal table in SAP.

************************************************************************

&----

*& Report ZTST_PGM02

&----

REPORT ZTST_PGM02.

Load iXML Lib.

type-pools: ixml.

class cl_ixml definition load.

data: G_IXML type ref to if_ixml.

data: STREAMFACTORY type ref to if_ixml_stream_factory.

data: ISTREAM type ref to if_ixml_istream.

data: DOCUMENT type ref to if_ixml_document.

data: PARSER type ref to if_ixml_parser.

data: LV_FILE_URL type rlgrap-filename.

You should provide the parameter for file name

LV_FILE_URL = 'D:\navps\input_xml.xml'.

types: begin of XML_LINE,

DATA(256) type x,

end of XML_LINE.

types: begin of TY_HEADER,

CUST_NAME(20) type c,

CARD_NO(20) type c,

TAX_AMOUNT(10) type c,

TOTAL_AMOUNT(10) type c,

end of TY_HEADER.

types: begin of TY_ITEM,

ITEM_NO(4) type n,

ITEM_ID(20) type c,

ITEM_TITLE(50) type c,

ITEM_QTY(10) type c,

ITEM_UPRICE(10) type c,

end of TY_ITEM.

data: GV_HEADER type TY_HEADER.

data: GT_ITEM type standard table of TY_ITEM with header line.

data: XML_TABLE type table of XML_LINE,

XML_TABLE_SIZE type i.

The next step is creating the main factory for the iXML library:

G_IXML = cl_ixml=>create( ).

Now Create Stream Factory

STREAMFACTORY = G_IXML->create_stream_factory( ).

upload a file from the client's workstation

call function 'WS_UPLOAD'

exporting

filename = LV_FILE_URL

filetype = 'BIN'

importing

filelength = XML_TABLE_SIZE

tables

data_tab = XML_TABLE

exceptions

others = 11.

wrap the table containing the file into a stream

ISTREAM = STREAMFACTORY->create_istream_itable( table = XML_TABLE

size = XML_TABLE_SIZE )

Get the file data as Stream

*istream = streamfactory->create_istream_uri( public_id = lv_file_url

system_id = lv_file_url ).

Create XML Document instance

DOCUMENT = G_IXML->create_document( ).

Create parser Object

PARSER = G_IXML->create_parser( stream_factory = STREAMFACTORY

ISTREAM = istream

DOCUMENT = document ).

Parse an XML document into a DOM tree

*parser->parse( ).

Parsing Error Processing

if PARSER->parse( ) ne 0.

if PARSER->num_errors( ) ne 0.

data: PARSEERROR type ref to if_ixml_parse_error,

STR type STRING,

I type i,

COUNT type I,

INDEX type i.

COUNT = PARSER->num_errors( ).

write: COUNT, ' parse errors have occured:'.

INDEX = 0.

while INDEX < COUNT.

PARSEERROR = PARSER->get_error( INDEX = index ).

I = PARSEERROR->get_line( ).

write: 'line: ', i.

I = PARSEERROR->get_column( ).

write: 'column: ', i.

STR = PARSEERROR->get_reason( ).

write: STR.

INDEX = index + 1.

endwhile.

endif.

Close the stream since it #s not needed anymore

call method ISTREAM->close( ).

clear ISTREAM.

Now try to make it look good
data : lv_size type sytabix,
lv_ret_code type sysubrc.

data: lo_xml_document type ref to cl_xml_document.

field-symbols: <fs_xml_data> type any table.

lo_xml_document = document.

call method lo_xml_document->get_as_table
importing
table = <fs_xml_data>
size = lv_size
retcode = lv_ret_code

*data: items type ref to if_ixml_node_collection.

*items = document->get_elements_by_tag_name( name = 'Item' ).

*data: iterator type ref to if_ixml_node_iterator,

node type ref to if_ixml_node.

*iterator = document->create_iterator( ).

*node = iterator->get_next( ).

*while not node is initial.

do something with the node

node = iterator->get_next( ).

*endwhile.

DATA : GV_NODE type ref to if_ixml_node.

DATA : GV_NODETEXT type STRING.

data: GV_FIRST_TIME.

GV_FIRST_TIME = 'X'.

GV_NODE = DOCUMENT.

GT_ITEM-item_no = 1.

perform GET_DATA tables GT_ITEM

using GV_NODE

changing GV_HEADER.

Last item is still not added.

append GT_ITEM.

write : GV_HEADER-cust_name,

GV_HEADER-card_no,

GV_HEADER-tax_amount,

GV_HEADER-total_amount.

loop at GT_ITEM.

write /:.

write : GT_ITEM-item_no,

GT_ITEM-item_id,

GT_ITEM-item_title,

GT_ITEM-item_qty,

GT_ITEM-item_uprice.

endloop.

----

FORM Get_data *

----

form get_data tables YT_ITEM structure GT_ITEM

using value(x_node) type ref to if_ixml_node

changing Y_HEADER type TY_HEADER.

data: INDENT type i.

data: PTEXT type ref to if_ixml_text.

data: STRING type string.

data: TEMP_STRING(100).

case X_NODE->get_type( ).

when if_ixml_node=>co_node_element.

STRING = X_NODE->get_name( ).

GV_NODETEXT = STRING.

when if_ixml_node=>co_node_text.

PTEXT ?= X_NODE->query_interface( IXML_IID_TEXT ).

if PTEXT->ws_only( ) is initial.

STRING = X_NODE->get_value( ).

case GV_NODETEXT.

when 'Customer'.

clear GV_HEADER.

when 'Name'.

move STRING to GV_HEADER-cust_name.

when 'Cardnum'.

move STRING to GV_HEADER-card_no.

when 'Tax'.

move STRING to GV_HEADER-tax_amount.

when 'Total'.

move STRING to GV_HEADER-total_amount.

Iteam details

when 'ID'.

move STRING to GT_ITEM-item_id.

when 'Title'.

move STRING to TEMP_STRING.

move TEMP_STRING to GT_ITEM-item_title.

when 'Quantity'.

move STRING to GT_ITEM-item_qty.

when 'UnitPrice'.

move STRING to GT_ITEM-item_uprice.

endcase.

endif.

endcase.

if GV_NODETEXT = 'Customer'.

clear GV_HEADER.

elseif GV_NODETEXT = 'Item'.

if GV_FIRST_TIME ne 'X'.

append GT_ITEM.

clear : gt_item.

GT_ITEM-item_no = gt_item-item_no + 1.

endif.

GV_FIRST_TIME = ' '.

endif.

Get the next child

X_NODE = x_node->get_first_child( ).

Recurse

while not X_NODE is initial.

perform GET_DATA tables GT_ITEM

using X_NODE

changing GV_HEADER.

X_NODE = x_node->get_next( ).

endwhile.

endform.

************************************************************************

Create an XML file with the following DATA given below and place it in the path mentioned above source code.

2. XML File DATA. You create an XML File in this format to test the same.

*************************************************************************************

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?>

- <Order>

- <Customer>

<Name>Bill Buckram</Name>

</Customer>

- <Manifest>

- <Item>

<Title>Duke: A Biography of the Java Evangelist</Title>

</Item>

- <Item>

<Title>100% Pure: Making Cross Platform Deployment a Reality</Title>

</Item>

- <Item>

<Title>Making the Transition from C++ to the Java(tm) Language</Title>

</Item>

- <Item>

<Title>Web Servers for Fun and Profit</Title>

</Item>

- <Item>

<Title>I Think Not: Dukes Likeness to the Federation Insignia</Title>

</Item>

</Manifest>

- <Receipt>

</Receipt>

</Order>

Re: Convert XML to SAP internal table

Former Member — Thu, 06 Jan 2011 02:38:43 GMT

Hi,

appreciate all the replies..will try all suggestions..

and will get back if its successful or not..

Re: Convert XML to SAP internal table

awin_prabhu — Thu, 06 Jan 2011 03:35:35 GMT

Hi Friend,

Check out Amit Mittal's answer in below thread.

Re: Convert XML to SAP internal table

Former Member — Mon, 10 Jan 2011 08:41:53 GMT

Hi,

when i try to download the file im getting from http_get function to presentation server.

i cannot open the file ..im getting error :

XML PARSE ERROR: Null or missing attribute value

Error occurs at or below this element stack:

<ss:Workbook>

<x:ExcelWorkbook>

mine is xml spreadsheet..

Re: Convert XML to SAP internal table

Former Member — Mon, 10 Jan 2011 11:50:32 GMT

Hi,

Hope this will hlp you

w_filename = p_file.

***********************************************************************

code to upload data from application server

***********************************************************************

OPEN DATASET w_filename FOR INPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc <> 0.

MESSAGE i000(ymmm) WITH 'File Not Exists In the Application Server'.

LEAVE LIST-PROCESSING.

ENDIF.

DO.

READ DATASET w_filename INTO is_xml_line.

IF sy-subrc EQ 0.

APPEND is_xml_line TO it_xml_table.

ELSE.

EXIT.

ENDIF.

ENDDO.

CLOSE DATASET w_filename.

code to find the table size

it_xml_tab[] = it_xml_table[] .

CALL FUNCTION 'SCMS_TEXT_TO_XSTRING'

EXPORTING

mimetype = '>'

IMPORTING

buffer = is_xmldata

TABLES

text_tab = it_xml_tab[]

EXCEPTIONS

failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

CALL FUNCTION 'SMUM_XML_PARSE'

EXPORTING

xml_input = is_xmldata

TABLES

xml_table = it_result_xml

return = it_return.

Thanks

Eswar

Re: Convert XML to SAP internal table

Former Member — Tue, 11 Jan 2011 02:30:22 GMT

Hi eswar,

i have tried as per suggestion..but im getting error from return table in call function 'SMUM_XML_PARSE'

XML_PARSER

|line 0 col 12-unexpected symbol: '>'

i copied the exact same code but why am i getting this error ?

is it possible the XML spreadsheet file im getting is not in correct format ?

Edited by: Guest101 on Jan 11, 2011 7:10 AM

Edited by: Guest101 on Jan 11, 2011 8:37 AM

Re: Convert XML to SAP internal table

Former Member — Wed, 12 Jan 2011 09:02:42 GMT

Finally..since i cannot parse it..try to download the file to presentation in '.xml' ..still have error too..cause i thot it suposed to be

in xml spreadsheet..

try my luck to save it in '.xls' ..and getting the file using cl_http and massage to itab...and it finally work!!!

and now after download..i will upload it back into sap...at least i get to rid of all the html codes...

thanks to all for ur help..=)