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

[Former Member]

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.

[harishputhran]

Dears,

Can any one send the process with steps for extraction xml data from a table field and load in to target table.

Regards,

Harish harish.puthran@outlook.com

[former_member187605]

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.

"

[former_member214617]

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

[former_member187605]

Don't reinvent the wheel. This is standard DS functionality.

[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.