| Description |
Steps:
# Begin new Integration Process
# Add export step
# Edit the step
# File Type = Fixed
# Database Connections = i21
# SQL Type = SQL Statement
# Paste the following CTE statement in the SQL Command field:
{color:#0000ff};WITH Split_Addr (strVendorId, strName,strAddress, xmlAddr){color}{color:#0000ff}AS{color}{color:#0000ff}-- Define the CTE query.{color}{color:#0000ff}({color}{color:#0000ff} SELECT strVendorId,{color}{color:#0000ff}strName,{color}{color:#0000ff} strAddress,{color}{color:#0000ff} CONVERT(XML,'' {color}{color:#0000ff} + REPLACE(strAddress, CHAR(10), '') + '') AS xmlAddr{color}{color:#0000ff} FROM vyuAPVendor{color}{color:#0000ff}){color}{color:#0000ff}-- Define the outer query referencing the CTE addr.{color}{color:#0000ff} SELECT CAST(strVendorId as CHAR(10)){color}{color:#0000ff} ,CAST(strName as CHAR(50)) as strName{color}{color:#0000ff} ,CAST(xmlAddr.value('/Addrs[1]/addr[1]','varchar(100)') as CHAR(30)) AS strAddress1{color}{color:#0000ff} ,CAST(xmlAddr.value('/Addrs[1]/addr[2]','varchar(100)') as CHAR(30)) AS strAddress2{color}{color:#0000ff} FROM Split_Addr{color}
# Attempt to select fields in the Field Mapping section.
!pastedImage_d104396_0.png!
!pastedImage_d104396_1.png!
|