Used this SET @SQL3 = 'INSERT INTO ' + '2009' + '.dbo.ALLFILENAMES(WHICHFILE) EXEC Master..xp_cmdShell ''' + @cmd1 + ''''

Its working now thank you

tried using this

declare @sql varchar(1000)

select @sql = 'INSERT dbo.mytest SELECT 'insert sales select ''test'' '

getting an error unclosed quoataion marks

Thank you , This really helped me for loading

The folder name and file name changes everytime

Folder name would be - posted 2015-01-16

Sub Folder - Hospital

sub sub folder- HOSPFY2010

Files -

hosp_2010_A.CSV

hosp_2010_R.CSV

hosp_2010_V.CSV

hosp_2010_D.CSV

All file go to different tables

like...

Thank you for the reply

We get the files quaterly , and delete the old files after the import as we a create a table and are kept in database...

R:-)

got it was using the wrong node

SELECT

HEADER.DATA.value('@CLM_ID', 'nvarchar(max)') AS CODE,

LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,

DIAG.DATA.value('@CODE','DECIMAL(18,1)') as DX

FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)

OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)

outer APPLY HEADER.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)

OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)

Thnaks...

SELECT

HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,

LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,

DIAG.DATA.value('@CODE','NVARCHAR(MAX)') as DX

FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)

OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)

OUTER APPLY INPUT.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)

OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)

but still gives me DX as NUll...

how do i get through the diag codes was working like this but shows null value

SELECT

HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,

LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,

DIAG.DATA.value('@DX_code','NVARCHAR(MAX)') as DX

FROM ...

Hi

Got an other issue how do i get to the node Line , use d a OUTERAPPLY but the data is showing null

SELECT

x.item.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,

g.line.value('@CLM_ID','NVARCHAR(MAX)') as CLM_IDLine

FROM...

Fund the solution used this

SELECT

header.value('@CLM_ID', 'NVARCHAR(MAX)') as CLM_ID,

header.value('@PROV_TYPE', 'VARCHAR(20)') as PROV_TYPE,

header.value('@ICD_VER', 'INT') as ICD_VERSION,

...

I changed it to

SELECT

x.item.value('@clm_id[1]', 'NVARCHAR(max)') AS CODE

FROM @x.nodes('//INPUT/HEADER') AS x(item)

But getting data as NULL

We have excel with 4 colums

5 nth select columa column b

10 5.16 ...

Now i get this error

Msg 241, Level 16, State 1, Procedure SP_SOAC_REPORT_SUMMARY, Line 7

Conversion failed when converting datetime from character string.

It says it cannot find sqlwb .