resolve xml passed as a single string to multiple records

[Former Member]

Hi

I am using the JDBC adapter to poll for a msg from the DB(by calling a stored procedure). The DB gives me a result set in the form of an xml (the full set of records is passed as a single string). Due to this the adapter is unable to recognise the file as an xml.

Is there any other method to get the file? Can I use the SOAP service(though I prefer not to..)

This is how my input file looks like:

<?xml version="1.0" encoding="utf-8" ?>

- <ns:Trial_MT xmlns:ns="http://test.com/test/SQLXML/Trial">

- <row>

<XMLResult><root><Product><ParentID>1</ParentID><ParentName>Parent 1</ParentName><Child><ChildID>1</ChildID><ChildName>Child 1</ChildName></Child><Child><ChildID>2</ChildID><ChildName>Child 2</ChildName></Child></Product><Product><ParentID>2</ParentID><ParentName>Parent 2</ParentName><Child><ChildID>4</ChildID><ChildName>Child 4</ChildName></Child><Child><ChildID>5</ChildID><ChildName>Child 5</ChildName></Child></Product><Product><ParentID>3</ParentID><ParentName>Parent 3</ParentName><Child><ChildID>3</ChildID><ChildName>Child 3</ChildName></Child></Product></root></XMLResult>

</row>

</ns:Trial_MT>

Thanks in advance

smitha

[Former Member · Accepted solution]

Hi Smitha,

Just before going ahead..check if you have valid xml input. You can do that by copy / paste your input data into any xml file. ( i.e text.xml) and try to open using Internet explorer.

Other option would be using xpath with java mapping to extract rek. information from incoming xml data and process as per your rek.

Check out this link for xpath with java,,

http://www.totheriver.com/learn/xml/xmltutorial.html

Let us know if you need more details.

Hope this will help.

Nilesh

[Former Member · Accepted solution]

Thanks Nilesh...Let me check that out

[Former Member]

Hi Smitha,

" Due to this the adapter is unable to recognise the file as an xml...." is not bcoz its not valid xml file, but the format / structure in which jdbc adapter receiving data is incorrect.

As suugested by Rodrigo it should be in the form of

"<MT_response> --> or other name

..<row>

......<field>

......<field2>

.....<field3>

..</row>

</MT_response>"

Now you have few options...

1. As u said go for soap.

2. You can write module which you can configure in JDBC sender channel and reformat data as per jdbc accepted format.

3. Simplest would be, as you said you are running sp on DB to get data, change sp to get the xml data in correct format( JDBC accptable format)

Hope this will help.

Thanks & Rgds,

Nilesh

[Former Member]

Hi All,

Thanks for your help. We decide to go with http adapter. The data would be send to XI over http and therefore no issues...

Thanks again,

-Smitha

[rodrigoalejandro_pertierr]

hi

your receiver structure is bad defined

the structure must be:

<MT_response> --> or other name

..<row>

......<field>

......<field2>

.....<field3>

..</row>

</MT_response>

why do you have sub-nodes?? all field must be at the same level.

remember each field correspond to one colum name in table DB.

try put all field at same level and tell me what happen.

Regard

Rodrigo

Edited by: Rodrigo Pertierra on Mar 27, 2008 2:11 PM

[Former Member]

Hi Rodrigo,

Thank you for your reply.

Now, that is my problem/requirement. The input structure that we get from the source system is like this. I need to find a way so that the JDBC adapter can read and recognize this structure. Is there some way to resolve this strange input? When i post the same input message via http client- it is going through...

[rodrigoalejandro_pertierr]

why dondt you try to do this by a Query

see this

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

[Former Member]

Hi Rodrigo,

Mine is the sender JDBC adapter.. these configs apply to the receiver, i believe...

[rodrigoalejandro_pertierr]

hi

sry i got confuse in my last post.

this is the structure you define

*<?xml version="1.0" encoding="utf-8" ?>

<ns:Trial_MT xmlns:ns="http://test.com/test/SQLXML/Trial">

<row>

<XMLResult><root><Product><ParentID>1</ParentID><ParentName>Parent 1</ParentName><Child><ChildID>1</ChildID><ChildName>Child 1</ChildName></Child><Child><ChildID>2</ChildID><ChildName>Child 2</ChildName></Child></Product><Product><ParentID>2</ParentID><ParentName>Parent 2</ParentName><Child><ChildID>4</ChildID><ChildName>Child 4</ChildName></Child><Child><ChildID>5</ChildID><ChildName>Child 5</ChildName></Child></Product><Product><ParentID>3</ParentID><ParentName>Parent 3</ParentName><Child><ChildID>3</ChildID><ChildName>Child 3</ChildName></Child></Product></root></XMLResult>

</row>

</ns:Trial_MT>*

copy the MT and put all fields at same level and modify the Communication channel configuration. do not put parent child, the response of a query never return nodes.

Thanlk