Tranfering data from XML to Sqlserver

, ,
1

hi,
While converting data from XML to Sqlserver I am using ,
XMLStringToXMLNode
XMLNodeToXMLDocument,
afterwards i am calling the insert adapter service which will insert the data in database.while i am executing the insert adapter service alone the data is inserting into database, but when i am calling this service with above service NULL values is stored for all the columns in the table.

Pls help me.
Thanks in advance,
priya

2

Are you aware of the structure of the incoming XML doc?
If so, After the service, performing XMLNodeToXMLDocument, map the values of the Adapter service’s i/p variables with the o/p variables of the XML. in the form %document/inoput1% etc.

3

yes, i have done this but it is taking object as input for adapter service.
xml format is similar to this
<!xml>
<record>
<eno>45678</eno>
<name>
<fname>peter</fname>
<lname>kanna</lname>
</name>
<address>
<street>electroniccity</street>
<city>Bangalore</city>
</address>
</record>

4

Hari,

Have you set the input isXML of the xmlStringToXMLNode service to true? If not, then your input string will not be treated as XML, this input variable can be - true, autoDetect or false (default = false). Also, I noticed that your xml tag looks like <!xml>… acc to standards, the xml document should begin with a minimal of <?xml>… so even if you set the isXML to autoDetect, your string will not be treated as XML.

If you have done the above and your service is still inserting NULL, try stepping through it… and see if the document created by the xmlNodeToDocument contains data. It is also possible that the your mapping from the document to the insert adapter service is wrong. With the xml sample in your post, the document produced will look like this:

 
document 
  |---record 
          |---eno 
          |---name 
          |    |---fname 
          |    |---lname 
          |---address 
               |---street 
               |---city

and not like

 
record 
    |---eno 
    |---name 
    |    |---fname 
    |    |---lname 
    |---address 
         |---street 
         |---city

Make sure you are mapping correctly. HTH,

Rohit