Inserting Data from an XML String Multiple Insertion

Guest · June 25, 2003, 6:03am

Hi All,

Good day.

I am trying to insert an Content of an XML String into an Table.

XMLString:

<prlnno>10</prlnno><prno>0010062733</prno><price>1100</price><qty>35</qty><descript>This is line item one…</descript>
<requestor>Requestor1</requestor><prlnno>20</prlnno><prno>0010062733</prno><price>1100</price><qty>35</qty><descript>This is line item two…</descript>
<requestor>Requestor2</requestor><prlnno>30</prlnno><prno>0010062733</prno><price>1100</price><qty>35</qty><descript>This is line item three…</descript>
<requestor>Requestor3</requestor>

I need to insert this in an Table called “WMPRDT” and it has the following fields.

PRLNNO
PRNO
PRICE
QTY
DESCRIPT
REQUESTOR

Following are my Steps:

stringToDocument
documentToRecord
insert

But somehow if i am having more set of Tag repeats i am getting the following Error,

[Microsoft][SQLServer 2000 Driver for JDBC]Unable to determine the type of the specified object.

Can anyone give an Suggestion for me in this regards , so that i can insert the above XML String as Three Records as the Tag.

regards
Brick

raymoser · June 25, 2003, 6:33am

You need to format a document list that contains the data that your table supports and is populated with your xml data:

Document
string - PRLNNO
string - PRNO
string - PRICE
string - QTY
string - DESCRIPT
string - REQUESTOR

The PRLNNO and Requestor fields will appear in each and the qty data will be different. Iterate through the document list and insert each record. Will this work?

Ray

Guest · June 25, 2003, 6:49am

Hi Ray,

Thanks for your Reply.

You mean that , stringToDocument Service should be used to create the Document List.
If not , Could you please guide me , for creating the Document List for the same.

regards
Brick

raymoser · June 25, 2003, 7:55am

The string to document service will take your xml file string and convert it to a node. Then you have to use document to record to obtain a record structure that matches your xml file structure. You should probably create a document (record) that represents the xml document. You can use the xml file to generate this, but the dtd is better and using a schema (xsd) file is best.

Then, you will map the contents of the new document that represents your xml file contents to the data structure that represents each record for insertion in the database.

Ray

Guest · June 25, 2003, 8:56am

Hi Ray,

I am also trying the same way , which you are telling.
But i am getting the same Error.

Attached is the Service which i am using.

Could you guide on this

regards
Ramesh

Zip File of the Service
dbtest1.zip (1.6 k)

raymoser · June 25, 2003, 9:13am

Ramesh,

I will not be able to look at this until later. Please advise of your email address and I will send this to you. I am working on v6.01 and will need to fire up v4.6 to look at this and cannot do so until later this evening. I am in Australia, GMT +10. Thanks.

Ray

raymoser · June 25, 2003, 3:23pm

Ramesh (Brick),

I tried to install your package, but it is empty. There are no interfaces or flows within the package structure that you uploaded.

You’ll have to try again.

Ray

Guest · June 26, 2003, 2:24pm

Hi Ray,

sorry for the Late reply , and attached is the Corrcet ZIP format of my Service.

regards
Ramesh (Brick)

My Service
postXMLString.zip (2.2 k)

raymoser · June 26, 2003, 3:04pm

I’ll take a look at it now and let you know.

Ray

raymoser · June 26, 2003, 3:20pm

I’ll need some sample data from you. Also, I had to load my commerce one package. I used the newer version, which has a different path than the one you are using.

Guest · June 26, 2003, 3:30pm

Hi Ray,
Thanks for your quick reply.
Here you go for the sample data…

Also i think you are aware of my table structure…

regards
Ramesh

XMLString:

<prlnno>10</prlnno><prno>0010062733</prno><price>1100</price><qty>35</qty><descript>This is line item 111one…</descript><requestor>ramesh111</requestor>
<prlnno>20</prlnno><prno>0010062734</prno><price>1200</price><qty>36</qty><descript>This is line item 222one…</descript><requestor>ramesh222</requestor>
<prlnno>30</prlnno><prno>0010062735</prno><price>1300</price><qty>37</qty><descript>This is line item 333one…</descript><requestor>ramesh333</requestor>
<prlnno>40</prlnno><prno>0010062736</prno><price>1400</price><qty>38</qty><descript>This is line item 444one…</descript><requestor>ramesh444</requestor>
<prlnno>50</prlnno><prno>0010062737</prno><price>1500</price><qty>39</qty><descript>This is line item 555one…</descript><requestor>ramesh555</requestor>
<prlnno>60</prlnno><prno>0010062738</prno><price>1600</price><qty>40</qty><descript>This is line item 666one…</descript><requestor>ramesh666</requestor>

raymoser · June 26, 2003, 3:35pm

I’m having trouble finding ANY references in this package to your original request. Perhaps you are mistaken? I will be unable to help you.

raymoser · June 26, 2003, 4:29pm

I’m starting to understand a little. The first package you uploaded is not complete or correct. The second package that you uploaded doesn’t seem to have anything to do with the above xml string.

Use the above xml example to create a record structure in the integration server environment. Make sure that the xml is fully qualified and not a snippet like above.
Then, string to document, document to record.
Loop through the multiple record sets. Within the loop, you will insert the data in the database.

HTH

Ray