CODEWITHSUNDEEP
sql-server
Sachin Prasad
Sachin Prasad

Reputation: 5411

SQL Error using using XML Path Method, Error converting data type varchar to float

I am trying to get Comma Separated String using XML Path Method

Tables:

CREATE TABLE [dbo].[tbl_BankList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [INST_ID] [bigint] NULL,
    [TITLE] [varchar](50) NULL,
    [ADDRESS_LINE1] [varchar](50) NULL,
    [CITY] [varchar](50) NULL,
    [STATE_CODE] [varchar](50) NULL,
    [ZIP] [varchar](50) NULL,
    [LOCATION_ID] [varchar](50) NULL,
    [ALOC] [varchar](50) NULL,
)

CREATE TABLE [dbo].[tbl_BankRTN](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [INST_ID] [bigint] NULL,
    [RTN] [float] NULL,
)

QUERY:

SELECT  bl.INST_ID,
  STUFF((SELECT distinct ',' + BR.[RTN]
         FROM tbl_BankRTN BR
         WHERE bl.INST_ID = BR.INST_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') RTN
FROM tbl_BankList bl;

Error - Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.

Upvotes: 4

Views: 3007

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Use CAST:

SELECT  bl.INST_ID,
  STUFF((SELECT distinct ',' + CAST(BR.[RTN] AS VARCHAR(1000))
         FROM tbl_BankRTN BR
         WHERE bl.INST_ID = BR.INST_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') RTN
FROM tbl_BankList bl;

or if SQL Server 2012+ CONCAT function:

SELECT  bl.INST_ID,
  STUFF((SELECT distinct CONCAT(',', BR.[RTN])
         FROM tbl_BankRTN BR
         WHERE bl.INST_ID = BR.INST_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') RTN
FROM tbl_BankList bl;

EDIT:

To avoid scientific format wrap float with STR:

Returns character data converted from numeric data.

STR ( float_expression [ , length [ , decimal ] ] )

SELECT  bl.INST_ID,
  STUFF((SELECT distinct ',' +  STR(BR.[RTN], 10,3)
         FROM tbl_BankRTN BR
         WHERE bl.INST_ID = BR.INST_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') RTN
FROM tbl_BankList bl;

Upvotes: 3

Related Questions