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.

Hi 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

Hi 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.