XML to JDBC with MS SQL 2005 XML Datatype

[Former Member]

Hi everyone.

We have a scenario where we get a XML message in PI which we want to store in a MS SQL Server 2005 database table using JDBC-adapter. The table has two columns: counterID and messageContent (with datatype XML).

Is this possible?

Best Regards

Niklas

[prateek]

Yes, of course it is possible. It is a usual receiver JDBC scenario. You may refer this for creating receiver side datatype structure.

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

Regards,

Prateek

[Former Member]

Thanks for your info Prateek.

I know how to configure a normal JDBC scenario. I just haven't used the 'XML Datatype on the SQL Server" before and since an xml file can contain all sorts of strange characters I just wanted to know if there are any obvious issues using this approach.

Do you have any good tips on how to convert the XML-content into one single field for inserting into the database?

Best Regards

Niklas

[former_member187339]

Hi,

Are you planning to insert the entire XML structure to DB field?

May be you can explain your doubt/problem a little bit more.

>>Do you have any good tips on how to convert the XML-content into one single field for inserting into the database?

Check this link

http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx

Regards

Suraj

[Former Member]

Hi Suraj.

Are you planning to insert the entire XML structure to DB field?

Yes that is exactly what I am trying to do.

May be you can explain your doubt/problem a little bit more.

In my first post I just wanted to know if anyone has done this before or has the experience to say that it is possible.

Check this link
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx

Thanks for googling it for me but I know how to configure the SQL Server...

Best Regards

Niklas

[former_member187339]

Hi,

Though I never tried. But it seems possible

1. Import the XSD of target (DB side)

2. Use XSLT mapping to put source into the target field

<xsl:text disable-output-escaping="yes"><![CDATA[<![CDATA http://<?xml version="1.0" encoding="UTF-8"?>]></xsl:text>
<xsl:copy-of select="*"/>
<xsl:text disable-output-escaping="yes"><!CDATA[]
]>
]]>

Check this blog http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3417200%29ID1124433650DB1059249387145227120...

Regards

Suraj

[Former Member]

Thank you for that link. It was very helpful! Seems like a pretty good way of doing this...

Best Regards

Niklas