| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to Captchure XML attributes and Insert into DB |
« View previous topic :: View next topic » |
| Author |
Message
|
| MB Developer |
 Posted: Thu Aug 07, 2014 10:13 pm Post subject: How to Captchure XML attributes and Insert into DB |
 |
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi Experts,
Greetings,
I have a sample xml file I want to insert all elements into DB.
So please give the solution ...
XML :
| Quote: |
<catalog>
<book id="bk101">
<bk101>
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<price>44.95</price>
</bk101>
</book>
<book id="bk102">
<bk102>
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<price>5.95</price>
</bk102>
</book>
</catalog> |
DB Elements are :
1.bookID
1.author
2.title
3.price
I will take Compute node ,already DSN(Data source) is created,table is created with above 4 fields.
_________________
Thanks.... |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Aug 07, 2014 10:36 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What have you tried so far?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| MB Developer |
| Posted: Fri Aug 08, 2014 4:51 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi smdavies99
Thanks for responding...
Based on this link I will write below code in my Compute node but when deployed it, it give an error(not deployed)..
| Quote: |
--CALL CopyMessageHeaders();
CALL CopyEntireMessage();
DECLARE price CHARACTER;
--DECLARE id CHARACTER;
DECLARE title CHARACTER;
DECLARE author CHARACTER;
--SET OutputRoot.XMLNSC.catalog.book.id = InputRoot.XMLNSC.catalog.book.id;
DECLARE id CHARACTER InputRoot.XMLNSC.catalog.book.id;
DECLARE k INTEGER 1;
DECLARE BookCatalog INTEGER CARDINALITY(InputRoot.XMLNSC.catalog.book[]);
WHILE (k <= BookCatalog) DO
SET OutputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)author = InputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)author;
SET OutputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)title = InputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)title;
SET OutputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)price = InputRoot.XMLNSC.catalog.book[k].(XMLNSC.Attribute)price;
SET k = k + 1;
MOVE BookCatalog LASTCHILD;
END WHILE;
INSERT INTO DataSource.BOOK_CATALOG(BOOKID,AUTHOR,TITLE,PRICE) VALUES (id,author,title,price); |
I will get below error in my Event Viewer
| Quote: |
( MB8BROKER.default ) ('.Insert_XMLAttribute_In_DB_MsgFlow_Compute.Main', '25.9') : A reference variable is required here.
The statement or function requires a reference variable. The name given must therefore be the name of a declared reference variable and cannot be extended (by a dot and another name).
Correct the syntax of your ESQL expression in node ''.Insert_XMLAttribute_In_DB_MsgFlow_Compute.Main'', around line and column ''25.9'', then redeploy the message flow. |
_________________
Thanks.... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Aug 08, 2014 5:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
BookCatalog is an integer, not a reference variable.
Don't use integers and cardinality to loop over trees.
Use a for loop to loop over the input. Use a separate reference variable that points to the current location in the output. |
|
| Back to top |
|
|
| McueMart |
| Posted: Fri Aug 08, 2014 7:09 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Well seeing as you have had a go, im happy to help with a few pointers.
- Use a reference (as mqjeff says) to iterate over the repeated elements.
- You seem to be creating an OutputRoot tree for no real reason. Dont do this unless you want to send the message to a downstream node.
- Your XML input message has unnecessary <bk101> etc sub-elements under <book>. I don't know why these are needed? If possible remove them.
Your code should be able to be as simple as this:
| Code: |
FOR aBook AS InputRoot.XMLNSC.catalog.book[] DO
DECLARE bookID CHARACTER aBook.id;
INSERT INTO Database.BOOK_CATALOG(BOOKID,AUTHOR,TITLE,PRICE) VALUES (bookID,aBook.{bookID}.author,aBook.{bookID}.title,aBook.{bookID}.price);
END FOR; |
|
|
| Back to top |
|
|
| MB Developer |
| Posted: Fri Aug 08, 2014 8:06 pm Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi McueMart,mqjeff..
Thanks for your help...
Now I understand where I can make mistakes and remove <book> element under book.
I used code given by McueMart
| Quote: |
FOR aBook AS InputRoot.XMLNSC.catalog.book[] DO
DECLARE bookID CHARACTER aBook.id;
INSERT INTO Database.BOOK_CATALOG(BOOKID,AUTHOR,TITLE,PRICE) VALUES (bookID,aBook.{bookID}.author,aBook.{bookID}.title,aBook.{bookID}.price);
END FOR;
|
In My DB BOOKID is inserted but remaining 3 fields are not inserted .
Now I will try and If anybody know give me suggestion...
_________________
Thanks.... |
|
| Back to top |
|
|
| McueMart |
| Posted: Sat Aug 09, 2014 9:40 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Well if you have now removed the <bkXXX> elements under <book>, your code will have to look like:
| Code: |
FOR aBook AS InputRoot.XMLNSC.catalog.book[] DO
INSERT INTO Database.BOOK_CATALOG(BOOKID,AUTHOR,TITLE,PRICE) VALUES (aBook.id,aBook.author,aBook.title,aBook.price);
END FOR; |
|
|
| Back to top |
|
|
| MB Developer |
| Posted: Sun Aug 10, 2014 10:33 pm Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi McueMart,
Yes it's working Really thanks for your support.....
_________________
Thanks.... |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
