JDBC - XML SQL format. Insert into SQL table when column names contain '@'

[Former Member]

Hi.

I need to generate a SQL insert statement using XML SQL format.

However, The column names of the target table are:

M5LITM

M5MMCU

M5@FDT

M5@QOP

M5UOM

M5@SRC

M5DCTO

I cannot define a Data Type Element names containing '@', because special characters are not permitted:

The value "M5@FDT" is not permitted for the attribute "name" of the "NCName" type in the "/schema/complexType("DT_xxxxx")/sequence/element("M5@FDT")" element

Any ideas on how I can do this?

Thanks in advance.

[Former Member · Accepted solution]

Hi Stefan

Thanks for your help.

Now I can see the XML and it looks as I would expect.

The tag names now represent the columns in the SQL database (with the @'s), and all tags are closed correctly.

It seems that the JDBC adapter doesn't like it because of the illegal characters.

When I try to open the extracted file in Internet Explorer, I get the same error as RWB is reporting

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

-

---

A name contained an invalid character. Error processing resource 'file://int008/transfer/out20110121-080209-099.xml'. Line...

<M5@FDT>20110307</M5@FDT>

-

---

^

....... any ideas?

[stefan_grube · Accepted solution]

@ is not allowed in XML names:

http://www.w3.org/TR/2006/REC-xml11-20060816/#NT-Name

So you have two options:

- use native SQL format

- use a stored procedure on database

Of course the best approach would be convincing database admins to change table field names.

[Former Member]

Very strange. The message is in error in the RWB, saying the following:

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

-

---

A name contained an invalid character. Error processing resource 'http://int008:51000/mdt/messagecontentservlet?messageKey=...

<ns0:MT_SAP_TO_EDRP_IB xmlns:ns0="http://rb/xi/clientproxy"><STATEMENT1><TABLENAME ACTION="INSERT"><TAB...*

...........................................................................................................

The Audit log says:

...........................................................................................................

Audit Log for Message: e4b3b506-7424-c943-ac05-00effb641935

Time Stamp Status Description

21.01.2011 00:10:08 Error Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189)(:main:, row=2, col=189) -> com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189).

21.01.2011 00:10:08 Error MP: exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189)(:main:, row=2, col=189) -> com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189)

21.01.2011 00:10:08 Error Exception caught by adapter framework: ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189)(:main:, row=2, col=189) -> com.sap.engine.lib.xml.parser.ParserException: Name expected: 0x40(:main:, row:2, col:189)

...........................................................................................................

When I try to view the source, it says "The XML source file is unavailable for viewing", so I can't actually see the resulting XML anywhere.

[stefan_grube]

define a file receiver channel and reroute the message to the file system. So you can see the structure.

[RaghuVamseedhar]

Hi Andy Cliff,

Yes, XML will be not well formed. That is the reason, in first place, Data type with attribute name containing '@' was not allowed. Please test it end to end and let's see if it works.

Regards,

Raghu_Vamsee

[Former Member]

Ok. Now the java mapping is in .jar file and looks good. But when I execute the mapping in the test tool, it says

Problem when builing tree - 'XML not well-formed'

[baskar_gopalakrishnan2]

Check whether all the target elements tags are properly closed.?

[Former Member]

OK.....

Edited by: Andy Cliff on Jan 21, 2011 12:43 AM

[RaghuVamseedhar]

Hi Andy Cliff,

Seems you have not zipped class file properly if you have used jar file, issue can be some thing else. Please check this tread [Link1|;.

Regards,

Raghu_Vamsee

[Former Member]

Hi Guys

Thanks for the suggestions.

Raghu_Vamsee:

I have have compiled your code and added it as a second mapping, but I get the following error:

LinkageError at JavaMapping.load(): Could not load class: DataSubString

java.lang.NoClassDefFoundError: DataSubString (wrong name: com/database/DataSubString)

Coul you suggest where I have gone wrong please?

Thanks in advance.

[RaghuVamseedhar]

Hi Andy Cliff,

I agree with Baskar Gopal, first graphical mapping and then use Java Mapping.

Step 1:- Create Data Type, where ever @ is there, replace it _ _ _ (3 underscores, because I do not think there will be 3 underscores in your data).

Step 2:- Do Graphical mapping.

Step 3:- In Java Mapping (below) replace _ _ _ with @.

package com.database;
import com.sap.aii.mapping.api.*;
import java.io.*;
import java.util.Map;

public class DataSubString implements StreamTransformation
{
    private Map map;
    String s;

    public void setParameter(Map map1)
    {    }

    public void execute(InputStream inputstream, OutputStream outputstream) throws StreamTransformationException
    {
        try
        {
            byte[] b = new byte[inputstream.available()];
            inputstream.read(b);
            String strContent = new String(b);
            strContent = strContent.replaceAll("___", "@");
            outputstream.write(strContent.getBytes());
        } catch (Exception exception)
        {
            exception.printStackTrace();
        }
    }
}

Regards,

Raghu_Vamsee

[baskar_gopalakrishnan2]

Interesting. Can you request the target system to go for proper naming convention in the database side?

If not go with the normal acceptabe field names in the data type side. After doing message mapping, you can do java mapping to change the field names. Basically you can do consecutive mapping. That is first message mapping and second java mapping.

Hope I answered your question.