Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    sql object - insert data from xpath stmts extract using designer service and sql objs
Go
New
Search
Notify
Tools
Reply
  
sql object - insert data from xpath stmts extract using designer service and sql objs
 Login/Join
 
Member
posted
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.


iWay 5.5 smsp1

windows 2003
 
Posts: 6 | Registered: October 19, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
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.


iWay 5.5 SMSP1 and 6.0.1 on Intel/Linux
 
Posts: 59 | Location: Birmingham, Blighty | Registered: October 30, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    sql object - insert data from xpath stmts extract using designer service and sql objs

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.