| Description |
I attempted to use the following CTE SQL statement to resolve the multi-line address issue (CHAR(10) delimitted). This statement works in SQL Management Studio but generates an Invalid Character error within the integration module:
{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}
*{color:#ff0000}Error that I get when I attempt a Run All:{color}*
{color:#0000ff} !pastedImage_d104397_0.png! {color}
|