Skip to Content
avatar image
Former Member

XML Data extraction from the table which is in sql server.

I have a table with one of the column as xml data. I want to extract the xml data from field.in BODS. I tried using several steps but no sucess.

Please let me know the steps.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 09, 2017 at 02:20 PM

    Hi Ramesh

    To extract the xml data from the database table..

    first you need to convert the column into long_to_varchar(column_nm), 1295261, 1), then use merge tansform, then replace all the special characters using replace_substr function, then create xml file at any shared location..


    WF should look like this and should be in loop:- DB Table -> query to convert column in long_to_varchar -> merge transform -> query transform to use replace -> target should be xml file.

    when all the xml file gets created then you can easily unnest the xml file and extract the data...

    Hope that will help.

    regards

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      screen.png

      Hi dec,

      The below is one of the column table where data is storing not in single file,as we are getting from OLTP.

      lot of of xml files are there and my intention is to extract data those files from that table using BODS.

      Thanks in advance.

      screen.png (4.8 kB)
  • May 09, 2017 at 03:05 PM

    RT*M.
    SAP Data Services Reference Guide, section 6.3.43 extract_from_xml:

    "

    This function allows you to extract XML data that is stored in one field of a database table into a query's output schema with Data Services' nested relational data model (NRDM) structure. Varchar data types are supported in the input column. You can use the following methods to extract data from clob and long data types.

    • Data Services converts a clob data type input to varchar if you select the Import unsupported data types as VARCHAR of size option when you create a database datastore connection in the Datastore Editor
    • If your source uses a long data type, use the long_to_varchar function to convert data to varchar.

    "

    Add comment
    10|10000 characters needed characters exceeded