September 10, 2008, 05:05 PM
petepeterssql object - insert data from xpath stmts extract using designer service and sql objs
In iway designer, I have two objects. a service object to extract data from an xml document that is processed by the PF. After extracting that information, I am trying to use the sql object to insert this dynamic data into a sql table.
My table is
CREATE TABLE [dbo].[wrtest](
[wrcode] [xml] NULL
) ON [PRIMARY]
my sql insert statement is in a procedure like this
A. create procedure sp_update_xml (@xmltp xml) - where this would be the xpath extract output for idoc_tp
as
INSERT INTO [BTS_Support].[dbo].[wrtest]
([wrcode])
VALUES
('idoc_tp,xml')
B.
INSERT INTO [BTS_Support].[dbo].[wrtest]
([wrcode])
VALUES ('idoc_tp,xml') < where this would be the xpath extract output for idoc_tp
where 'idoc_tp' is an xpath output from name = idoc_tp and xpath statement = XPATH(/sap:INVOIC02/IDOC/EDI_DC40/RCVPRN)used in a service object extract
I am not sure what is the best way to setup the sql object insert. Just a plain insert with a runtime parameter from the xpath extract with the xml document or as a stored procedure with a runtime variable
either way, I don't have all of the proper syntax to use in the designer/sql object to dynamically take xpath output and use as a runtime parameters to insert data into a table.
October 14, 2008, 03:40 PM
KerryHi petepeters,
I passed this one to our iWay people and heard the response: this seems like involved issue, so the best channel for assistance would be to open a case with Information Builders' Customer Support Services. You may either call at 1-800-736-6130, or access online at
InfoResponse at any time.
Hope this helps.
Cheers,
Kerry
November 18, 2008, 04:58 AM
AdeHHi Pete,
Not sure I have grasped your question completely, so apologies if this isn't what you're asking..!
To pass values dynamically to static SQL in an SQL object, code the "Statement" tab like this:
INSERT INTO [BTS_Support].[dbo].[wrtest]
([wrcode])
VALUES ('?idoc_tp,xml')
The question mark signifies that the value is to be collected dynamically.
Now add an entry on the "User Defined Properties" tab of the SQL object:
Name = idoc_tp
Type = string
Value = XPATH(/sap:INVOIC02/IDOC/EDI_DC40/RCVPRN)
So I guess you wouldn't need the first service object to extract the XPATH value anymore either.
Apologies if I've sent you on a goose chase.